Converting a query to dynamic SQL with linked server variable

Question

I have a stored procedure that I need to convert so that it reads a linked server variable from a table. From what I know, the only way to do so is to use dynamic SQL. The problem is that I'm failing to convert my query.

Original query:

SET @var1 = ''
SELECT  @var1 = RECEIVER        
FROM    databse1.dbo.table1 
WHERE   SAPNUMBER = @var2

Converted query:

SET @srv = (SELECT server_name
            FROM Configuration.dbo.Server_Switch)

SET @var1 = ''
exec (  
'SELECT ' + @var1 + '= RECEIVER
FROM ' + @srv + '.databse1.dbo.table1 
WHERE SAPNUMBER =' + @var2
)

The error that I'm getting is:

Incorrect syntax near '='

@var1 and @var2 are variables declared beforehand. I'm pretty sure there's a problem in assigning values to these variables in dynamic SQL, hence the '=' error. Can I get some help in converting this query?

Why do you think that dynamic sql would solve any problem at all? The only thing it would do was increase the possibility of problems. Imagine if someone stored a Bobby Tables in that Server_Switch table .... — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 19, 2017 at 13:35

Zohar Peled · Accepted Answer · 2017-01-19 13:45:52Z

4

One way to solve it is to use sp_executeSql with an output parameter:

SET @srv = (SELECT server_name
            FROM Configuration.dbo.Server_Switch)

DECLARE @sql nvarchar(500),
        @ParmDefinition nvarchar(500);

SET @sql = 
'SELECT @output = RECEIVER
FROM ' + @srv + '.databse1.dbo.table1 
WHERE SAPNUMBER =' + @var2

SET @ParmDefinition = N'@var1 varchar(100) OUTPUT';

EXEC sp_executesql @Sql, @ParmDefinition, @var1=@output OUTPUT;

answered Jan 19, 2017 at 13:45

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Juozas · Accepted Answer · 2017-01-19 13:49:55Z

0

Please provide more info. We need variables @var2, @var1 declaration, types of variables. If @var2 is varchar, then please update your query in followoing way:

declare @sql nvarchar(4000) = 
'SELECT ' + @var1 + '= RECEIVER
FROM ' + @srv + '.databse1.dbo.table1 
WHERE SAPNUMBER =' + char(39) + @var2 + char(39);

exec(@sql);

P.S. if @var2 numeric:

declare @sql nvarchar(4000) = 
'SELECT ' + @var1 + '= RECEIVER
FROM ' + @srv + '.databse1.dbo.table1 
WHERE SAPNUMBER =' + @var2;
exec(@sql);

edited Jan 19, 2017 at 13:49

answered Jan 19, 2017 at 13:43

Juozas

94310 silver badges18 bronze badges

1 Comment

Juozas Over a year ago

And finally I understood, that your target was to get value in @var1, not get the recordset. At this case you need to use sp_executesql with output, as said Zohar Peled

Collectives™ on Stack Overflow

Converting a query to dynamic SQL with linked server variable

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related