使用链接的服务器变量将查询转换为动态SQL [英] Converting a query to dynamic SQL with linked server variable
本文介绍了使用链接的服务器变量将查询转换为动态SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个需要转换的存储过程,以便它从表中读取链接的服务器变量。据我所知,唯一的方法是使用动态SQL。问题是我无法转换查询。
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.
原始查询:
SET @var1 = ''
SELECT @var1 = RECEIVER
FROM databse1.dbo.table1
WHERE SAPNUMBER = @var2
转换后的查询:
SET @srv = (SELECT server_name
FROM Configuration.dbo.Server_Switch)
SET @var1 = ''
exec (
'SELECT ' + @var1 + '= RECEIVER
FROM ' + @srv + '.databse1.dbo.table1
WHERE SAPNUMBER =' + @var2
)
我得到的错误是:
Incorrect syntax near '='
@ var1和@ var2是事先声明的变量。我敢肯定,在动态SQL中为这些变量分配值时会出现问题,因此会出现 =错误。我可以在转换此查询方面获得帮助吗?
@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?
推荐答案
一种解决方法是使用带有输出参数的sp_executeSql:
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;
这篇关于使用链接的服务器变量将查询转换为动态SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文