I want to return a value from a dynamic linked server stored procedure.
--storedprocedure on the remote linked server ALTER PROCEDURE [dbo].[SPTEST] @DocNumOut VARCHAR(20) output AS BEGIN SET NOCOUNT ON; SELECT @DocNumOut=1; RETURN @DocNumOut END
On the local server, how can I retrieve the value of @DocNumOut
? I have trial many combination using EXEC
, openquery
, EXEC sp_executesql
, but none can return the value into my local variable, @DocNumberOUT
.
--local server DECLARE @DocNumberOUT as int; DECLARE @SQLCMD AS VARCHAR(2000)='EXEC LinkedServer.MyDB.dbo.SPTEST' EXEC @DocNumberOUT= sp_executesql @SQLCMD --Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Advertisement
Answer
Use OUTPUT parameter on your Stored Procedure and don’t use the return value.
Use the proper data type. Don’t use varchar
for everything. Since you assigned an integer to the variable, declare it as integer
.
ALTER PROCEDURE [dbo].[SPTEST] @DocNumOut INT output AS BEGIN SET NOCOUNT ON; SELECT @DocNumOut=1; END
On the calling side, the error message is telling you that @SQLCMD
should declare as NVARCHAR
. Using sp_executesql
is the right choice. But you should also define the parameters and pass in the variable also. @DocNumOut
is the name of the parameter that you defined in SPTEST
. @DocNumberOUT
is your local variable
DECLARE @DocNumberOUT as int; DECLARE @SQLCMD AS NVARCHAR(2000); SET @SQLCMD = 'EXEC [LinkedServer].[MyDB].[dbo].[SPTEST] @DocNumOut = @DocNumberOUT OUTPUT' EXEC sp_executesql @SQLCMD, N'@DocNumberOUT INT OUTPUT', @DocNumberOUT OUTPUT SELECT @DocNumberOUT