using table/field names stored in local variables

Question

Post reply

using table/field names stored in local variables

Anatoli Lightfoot

SSC Journeyman

Points: 78
More actions
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.

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

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

Brent Leslie SSC-Addicted Points: 416 More actions · Answer 1

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...

Hans Lindgren SSChampion Points: 10496 More actions · Answer 2

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

Vladan SSC-Insane Points: 22072 More actions · Answer 3

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!

Anatoli Lightfoot SSC Journeyman Points: 78 More actions · Answer 4

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