Converting A Query To Dynamic Sql With Linked Server Variable

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

Solution 1:

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 ='+@var2SET@ParmDefinition= N'@var1 varchar(100) OUTPUT';

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

Solution 2:

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);