使用输出变量动态执行存储的Proc
使用输出变量动态执行存储的Proc
提问于 2020-07-13 21:07:31
我有一个脚本，它使用链接服务器引用和OPENQUERY()语句调用不同SQL Server上的存储过程。为了演示，我在远程服务器上执行了以下过程：
-- ON SQL_SVR Remote Server
CREATE PROC [dbo].[MyProc]
( @Pram_A NVARCHAR(4) OUTPUT
,@Pram_B NVARCHAR(4) OUTPUT
,@Pram_C NVARCHAR(4) OUTPUT
,@Pram_D NVARCHAR(4) OUTPUT
)
AS
SELECT
@Pram_A = 'foo'
,@Pram_B = 'bar'
,@Pram_C = 'this'
,@Pram_D = 'that'
GO现在，我想调用此过程并检索它返回的所有四个值，因此我在本地服务器上编写了以下脚本：
-- These variables are final resting places for my data
DECLARE
@Pram_1 NVARCHAR(4)
,@Pram_2 NVARCHAR(4)
,@Pram_3 NVARCHAR(4)
,@Pram_4 NVARCHAR(4);
DECLARE
@SQL NVARCHAR(MAX)
-- I will pass values from the remote proc to the dynamic sql through some output parameters, which are middle men between the remote Alpha and the local Numeric params
,@Parms NVARCHAR(MAX) = '@Pram_A1 NVARCHAR(4) OUTPUT, @Pram_B2 NVARCHAR(4) OUTPUT, @Pram_C3 NVARCHAR(4) OUTPUT, @Pram_D4 NVARCHAR(4) OUTPUT'
,@LinkedServer VARCHAR(50) = '[SQL_SVR]';
-- The dynamic sql expects to get the middle-men parameters, which it will fill with the values it gets for it's alpha-style params
SET @SQL = 'SELECT * FROM OPENQUERY(' + @LinkedServer + ',''EXEC [dbo].[MyProc] @Pram_A = @Pram_A1 OUTPUT, @Pram_B = @Pram_B2,@Pram_C = @Pram_C3,@Pram_D = @Pram_D4'')';
-- Take whatever the remote proc puts into the middle-men params and put it into our local numeric params
EXEC sp_executesql @SQL, @Parms
,@Pram_A1 = @Pram_1 OUTPUT
,@Pram_B2 = @Pram_2 OUTPUT
,@Pram_C3 = @Pram_3 OUTPUT
,@Pram_D4 = @Pram_4 OUTPUT;
-- show the contents of the local, numeric params.
SELECT
@Pram_1 AS P1
,@Pram_2 AS P2
,@Pram_3 AS P3
,@Pram_4 AS P4;据我所知，这都是对参数传递(诚然有点复杂)的正确使用，声明本地参数，将它们传递给动态sql，并在动态sql中使用远程脚本上声明的参数。然而，当我运行上面的命令时，我得到了以下警告和错误：
--OLE DB provider "SQLNCLI11" for linked server "PRODSQL-V2" returned message "Deferred prepare could not be completed.".
'Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Pram_A1".'我已经广泛地寻找了解决方案，但我所看到的一切似乎都表明我的脚本是正确的。我知道我一定是误解了什么，犯了一个错误，但我无论如何也看不出是什么！你能看到我做错了什么吗?或者解释一下脚本可能失败的可能原因？
Note1:使用动态sql和变量服务器名的原因在非常简单的示例脚本中没有显示出来。
Note2:本地脚本在SQL Server 14.0.3048.4上，远程脚本在13.0.4001.0上
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-07-13 21:26:13
您可以在每个“作用域”中使用相同的参数名称，这简化了事情，而且您必须使用sp_executesql而不是OPENQUERY来绑定输出参数。使用OPENQUERY，您必须在结果集中返回值。
所以就像这样：
-- ON SQL_SVR Remote Server
CREATE OR ALTER PROC [dbo].[MyProc]
( @Pram_A NVARCHAR(4) OUTPUT
,@Pram_B NVARCHAR(4) OUTPUT
,@Pram_C NVARCHAR(4) OUTPUT
,@Pram_D NVARCHAR(4) OUTPUT
)
AS
SELECT
@Pram_A = 'foo'
,@Pram_B = 'bar'
,@Pram_C = 'this'
,@Pram_D = 'that'
GO
-- These variables are final resting places for my data
DECLARE @Pram_A NVARCHAR(4)
,@Pram_B NVARCHAR(4)
,@Pram_C NVARCHAR(4)
,@Pram_D NVARCHAR(4);
declare @LinkedServer VARCHAR(50) = 'SQL_SVR';
declare @SQL NVARCHAR(MAX) = concat('
EXEC ',quotename(@LinkedServer),'.[TempDb].[dbo].[MyProc] @Pram_A = @Pram_A OUTPUT,
@Pram_B = @Pram_B OUTPUT,
@Pram_C = @Pram_C OUTPUT,
@Pram_D = @Pram_D OUTPUT
')
declare @Parms NVARCHAR(MAX) = '@Pram_A NVARCHAR(4) OUTPUT, @Pram_B NVARCHAR(4) OUTPUT, @Pram_C NVARCHAR(4) OUTPUT, @Pram_D NVARCHAR(4) OUTPUT'
exec sp_executesql @sql, @Parms, @Pram_A = @Pram_A OUTPUT,
@Pram_B = @Pram_B OUTPUT,
@Pram_C = @Pram_C OUTPUT,
@Pram_D = @Pram_D OUTPUT
SELECT
@Pram_A AS P1
,@Pram_B AS P2
,@Pram_C AS P3
,@Pram_D AS P4;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62876514
复制相关文章
相似问题
腾讯云开发者
