Dynamic SQL and table names

[psin]

Hi all,

I have been attempting to construct some dynamic SQL using a table name as a parameter, and call it using sp_executesql, in SQL Server 2005.

According to all of the searches and articles I have read today, the following should work:

DECLARE @rownum INT
DECLARE @tabNam NVARCHAR(100)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

SET @rownum = 35
SET @tabNam = 'AdminScriptHistory'

SELECT @SQLString = N'SELECT * FROM @table where executionorder=@row'
SET @ParmDefinition = N'@table VARCHAR(100), @row INT'

EXEC sp_executesql @SQLString, @ParmDefinition, @tabNam,@rownum

However, it fails with:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@table".

Anybody know why?

Cheers

 

[ca8msm]

Because you haven't escaped out of the string, it is looking for a table named "@table".


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[bborissov]

Don't pass Table name as parameter to sp_executesql. Built string with it:

DECLARE @rownum         INT
DECLARE @tabNam         NVARCHAR(100)
DECLARE @SQLString      NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

SET @rownum = 35
SET @tabNam = 'AdminScriptHistory'

SELECT @SQLString = N'SELECT * FROM '+@table+
                     ' where executionorder=@row'
SET @ParmDefinition = N'@row INT'

EXEC sp_executesql @SQLString, @ParmDefinition, @rownum

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[psin]

I need to be able to call the same SQL many times, with different parameters, hence the parameter name in the SQL string.

According to the documentation this should work.