Post reply

using table/field names stored in local variables

  • July 24, 2006 at 1:03 am

    #115932

    Hi there people,

    I had a bit of a look around the site and couldn't find this answered anywhere, so here goes:

    I'm trying to find a way to run a SELECT or INSERT statement on a table whose table name is stored in a local variable. It appears on the surface that this is not possible though, for example:

    DECLARE @test-2 varchar(20)

    SET @test-2 = 'testtable'

    SELECT * FROM @test-2

    Gives the error:

    Server: Msg 137, Level 15, State 2, Line 3

    Must declare the variable '@test'.

    So I'm assuming that it wants to treat @test-2 as a table variable. Is there another way to accomplish this that I'm missing?

    Thanks,

    Anatoli

    EDIT: I should've added: I tried using the sysname datatype with the same result.

  • July 24, 2006 at 2:05 am

    #650995

    Hay,

    To do this use dynamic SQL with a built in stored proc... to get the example you posted going:

    DECLARE @SQL nvarchar(500)

    DECLARE @test-2 varchar(20)

    SELECT @test-2 = 'testtable'

    SELECT @SQL = 'SELECT * FROM ' + @test-2

    EXEC sp_executesql @SQL

    will work with INSERTs, UPDATEs etc just as good...

     

  • July 25, 2006 at 3:36 am

    #651279

    Remember that an dynamic SQL is always executed with the same permissions as the user executing the dynamic SQL has.

    I.e. if in an SP you have dynamic SQL you have to give higher permissions then you probably want to the user that needs to execute the SP.

    Regards,

    Hanslindgren

  • July 25, 2006 at 7:13 am

    #651320

    This question is quite common on these forums, you should be able to find some answers... but in short, the answer is NO, you can't do that in SQL Server. In most cases, need for similar operations suggests bad database design - unfortunately, often you can't influence it and have to live with the database as it is.

    There are workarounds, like dynamic SQL, but they have also disadvantages and are not suitable for many situations. If you decide to use it, be sure to read about dynamicSQL first, e.g. here: http://www.sommarskog.se/dynamic_sql.html

    Good luck!

  • July 25, 2006 at 5:30 pm

    #651514

    Thanks heaps for the advice - I don't need to worry about the permissions issue since I'm the only one who will be running these queries I'm writing. I'll have a read of that linked article though, for reference.

    Thanks again,

    Anatoli

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply