dynamic SQL problem

Question

Viewing 6 posts - 1 through 7 (of 7 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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]

ksullivan SSCrazy Points: 2346 More actions · Answer 2

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

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 3

thank you for explaining this, really appreciated.

Sergei

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

cenko SSC Veteran Points: 281 More actions · Answer 5

How can I use the resulting value from @table variable in a Select/Update/Insert statement after the EXECUTE? Or can I?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

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]