Professional Web Applications Themes

SP with Table name as Variable - Microsoft SQL / MS SQL Server

Hi, I am trying to write a SP to simply count rows from a table. Its just a bit of learning for me, and although I have written other SPs which pass values, I am having trouble with this. Here is the code I am testing in Query yser: Declare MyField varchar (128) Declare MyTable table Set MyTable = 'DMS_Ref_DBobject_Types' Set MyField = 'ObjectTypeID' SELECT Count(Myfield)FROM MyTable Here is the error message: Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'Set'. Server: Msg 137, Level 15, State 1, Line 5 Must declare the variable ...

  1. #1

    Default SP with Table name as Variable

    Hi,

    I am trying to write a SP to simply count rows from a table. Its just a bit
    of learning for me, and although I have written other SPs which pass values,
    I am having trouble with this.

    Here is the code I am testing in Query yser:



    Declare MyField varchar (128)
    Declare MyTable table

    Set MyTable = 'DMS_Ref_DBobject_Types'
    Set MyField = 'ObjectTypeID'

    SELECT Count(Myfield)FROM MyTable


    Here is the error message:

    Server: Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'Set'.
    Server: Msg 137, Level 15, State 1, Line 5
    Must declare the variable 'MyTable'.


    Jonesgj Guest

  2. #2

    Default Re: SP with Table name as Variable

    Ooops,

    Sorry the code shown below is just one of a number of attempts to
    investigate where I am going wrong
    Here is my first attempt.

    Declare MyField varchar (128)
    Declare MyTable varchar (128)
    Set MyTable = 'DMS_Ref_DBobject_Types'
    Set MyField = 'ObjectTypeID'

    SELECT Count(Myfield)FROM MyTable

    Error

    Server: Msg 137, Level 15, State 2, Line 7
    Must declare the variable 'MyTable'.


    "Jonesgj" <com> wrote in message
    news:bfar87$3u6$btinternet.com... 
    bit 
    values, 


    Jonesgj Guest

  3. #3

    Default Re: SP with Table name as Variable

    Hi Jones

    I understand that you are writing a proceure to fetch
    the number of rows in a table in this case you would have
    to use dynamic sql


    Declare MyField varchar (128)
    ,MyTable varchar (128)
    ,Strsql nvarchar(3000)

    Set MyTable = 'Tablename'
    Set MyField = 'FieldID'

    SET Strsql='SELECT Count('+Myfield+') FROM '+MyTable+''
    execute (strsql)

    HTH

    Viswanath





     
    attempts to [/ref]
    table. Its just a [/ref]
    SPs which pass 
    >
    >
    >.
    >[/ref]
    Vishnat Guest

  4. #4

    Default Re: SP with Table name as Variable

    Thanks Erland,

    I thought it might be to do with the Table name aspect. I'll take on board
    what you say about the optimiser.

    In the meantime I have got the code below to work.

    I am writing an SP which will be triggered from a VB.NET App, and just
    thought I could re-use the SP for several small functions such as checking
    for count of rows, or the existance of a record.

    What do you think?




    Declare SQLString as nvarchar (400)
    Declare MyField Nvarchar (128)
    Declare MyTable Nvarchar(128)



    Set MyTable = N'DMS_Ref_DBobject_Types'
    Set MyField = N'ObjectTypeID'




    set SQLString = N'SELECT Count(' + MyField + ') FROM ' + MyTable



    print SQLString

    EXEC sp_executesql SQLString




    "Erland Sommarskog" <se> wrote in message
    news:0.0.1... 
    >
    > Since you are learning I will just say: "you cannot do this". This is not
    > really true, since there are ways to build statements dynamically.[/ref]
    However, 


    Jonesgj Guest

Similar Threads

  1. Assigning a table value from a dataset to a variable
    By blitzed2010 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 29th, 07:37 PM
  2. Insert ASP variable into SQL table
    By Mintyman in forum Dreamweaver AppDev
    Replies: 4
    Last Post: April 1st, 09:39 AM
  3. Replies: 2
    Last Post: October 13th, 03:55 AM
  4. Usage and Benefit of Table Variable
    By CDARS in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 08:04 AM
  5. Abstraction of table variable declaration
    By SiWhite in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 3rd, 09:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139