sqlsql-servert-sqlopenquerysp-executesql

Dynamically Executing Stored Proc with output variables


I have a script that calls a stored procedure on a different SQL Server,using a Linked Server reference and an OPENQUERY() statement. For demonstration, I have made the following procedure, on the remote 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

Now, I would like to call this procedure and retrieve all four values that it returns, so I have written the following script on the local server:

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

So far as I am aware, this is all correct usage of the (admitedly slightly complex) parameter passing, declaring the local parameters, passing them to the dynamic sql and inside the dynamic sql using those declared on the remote script. However, when I run the above, I am given the following warning and error:

--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".'

I have searched extensively for a solution, but everything I look at seems to imply that my script is correct. I know I must have misunderstood something and made a mistake but I cannot for the life of me see what! Can you see what I have done wrong or explain a possible reason why the script might be failing?

Note1: There are reasons for using dynamic sql and a variable server name that are not shown in the very simplified example script.

Note2: The local script is on SQL Server 14.0.3048.4, the remote on 13.0.4001.0


Solution

  • You can use the same parameter names in each "scope" which simplifies things, and you have to use sp_executesql not OPENQUERY to bind output parameters. With OPENQUERY you'd have to return the values in a resultset.

    So something like this:

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