March 19, 2008 at 5:16 pm
please explain to me why this query
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level',
N'@level tinyint',
@level = 109;
works OK, while this one
EXECUTE sp_executesql
N'SELECT * FROM @TableName
WHERE EmployeeID = 1',
N'@TableName nvarchar(255)',
@TableName= 'AdventureWorks.HumanResources.Employee ';
gives me error message:
**Must declare the table variable "@TableName".*
I vaguely remember something about tables names being difficult to call from dynamic SQL ...
Thanks,
March 19, 2008 at 7:31 pm
You cannot parametize object names in SQL.
Try this:
Declare @TableName varchar(255)
Select @TableName = 'AdventureWorks.HumanResources.Employee '
Declare @sql varchar(max)
Select @sql = N'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = 1'
EXECUTE sp_executesql @sql
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 19, 2008 at 7:34 pm
If you have to substitute tables, then do the substitution like this:
declare @TableName nvarchar(255), @sql nvarchar(500)
set @TableName = 'AdventureWorks.HumanResources.Employee';
set @sql = N'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = 1 '
-- print @sql
EXECUTE sp_executesql @sql
Table variables have to be created within the dynamic sql
declare @TableName nvarchar(255), @sql nvarchar(500)
set @TableName = 'AdventureWorks.HumanResources.Employee';
set @sql = N'declare @table table (xxx int) insert into @table values (1) select * from @table
SELECT * FROM ' + @TableName + ' WHERE EmployeeID = 1 '
-- print @sql
EXECUTE sp_executesql @sql
March 20, 2008 at 6:00 am
thank you for explaining this, really appreciated.
Sergei
March 20, 2008 at 10:41 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2008 at 10:58 am
How can I use the resulting value from @table variable in a Select/Update/Insert statement after the EXECUTE? Or can I?
March 21, 2008 at 12:34 pm
You would have to keep it within the Dynamic Sql batch, as per ksullivan's example.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply