October 15, 2002 at 8:35 am
We have a table name stored in a variable. So far we've needed dynamic SQL to execute queries against it. For example:
DECLARE @Qry VARCHAR(512)
DECLARE @Table VARCHAR(20)
SET @Table = 'MyTable'
SET @Qry = 'SELECT * FROM '
...
SET @Qry = @Qry + @Table
EXEC (@Qry)
What I want to do is something like this:
SELECT * FROM (@MyTable)
Can it be done?
Steve
Steve Miller
October 15, 2002 at 10:16 am
October 15, 2002 at 11:57 am
Clear, concise, and to the point. 🙂
(Thanks)
Steve (<sigh>. Now I have to figure out how to code this thing.)
Steve Miller
October 15, 2002 at 3:47 pm
Further more dynamic SQL involving variables should be executed with sp_executesql
i.e select * from (@table) where column = @value.
If @table and @value are passed in then the code should be
declare @sql nvarchar(200)
set @sql = 'select * from ' + @table + ' where column = @value'
exec sp_executesql @sql ,N'@value int', @value
this allows for proper parameterisation by the sql engine
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 15, 2002 at 3:58 pm
Yup. I was just trying to get an example out the door so people would know what I'm talking about. So I was being a little lazy.
Steve
Steve Miller
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply