July 18, 2004 at 9:10 am
I have a dynamically generated table and i need to query from that table. So I first store the tablename in a variable and then use statement "select * from @tname". But, this gives an error. SQL Server wants a constant to be used after from keyword
July 18, 2004 at 9:52 am
hey,
SQL Server does not allow variables for table names in the FROM clause, except for table variables.
Try to do the queries without relying in a dynamic table name...
July 18, 2004 at 11:13 am
It is best not to write dynamic sql as you need access to the tables directly, hence it is open to abuse from sql injection etc.
In saying that you can write your select statement into a variable and execute your variable. There are two ways of running the code. The best way would be to use sp_executesql which is parameter driven, hence you can pass in your tablename. The other is EXEC (@string)
Steven
July 19, 2004 at 4:22 am
you can do the following BEGIN Declare @var1 nchar(200), @var2 nchar(300) set @var1 = 'table name' set @var2 = 'Select * from ' + @var1 + "where where_cond" EXECUTE sp_executesql @var2 END |
July 23, 2004 at 10:09 am
If you only need the table temporarily, you could could create #tempTable which you can then use in SELECT * FROM #tempTable
July 24, 2004 at 9:20 pm
The Example From Essea is Correct i use it all the Time for all of the tables I use for drop down boxes. The only Caveat is that you have to grant select Permissions on the table for the login in addition to the stored procedure because Dynamic queries use the security of the login when they are executed.
Just thought I would mention that for when you try to deploy and the remote client pukes on you.
Tal Mcmahon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply