Search code examples
sqlstored-proceduresdynamic

Using OUTPUT Parameters within Dynamic SQL within Stored Procedures- Possible?

I have a SP that I have created to check for validations and return an OUTPUT Parameter of 0 (No Error) or 1 (Error). However, I have to run this SP within Dynamic SQL since it will be ran through a loop of different data.

Can I pull the OUTPUT from the SP through the EXEC sp_executesql @SQL?

I can not post the actual code, but I can give an example..

DECLARE
@SQL nVARCHAR(4000),
@SPName VARCHAR(200),
@Parm1 VARCHAR(100),
@Parm2 VARCHAR(100),
@Parm3 VARCHAR(100),
@ParmDefinition nVARCHAR(400),
@Error nVARCHAR(1)

SELECT
    @SPName = 'spExample1',
    @Parm1  = '000000',
    @Parm2  = '111111',
    @Parm3  = '@Err=@Error OUTPUT',

SET @SQL = 'EXEC ' + @SPName + ' ' + @Parm1 + ',' + @Parm2 + ',' + @Parm3 + '

SET @ParmDefinition = N'@Err2 nVARCHAR(1) OUTPUT'
EXEC sp_executesql @SQL, @ParmDefinition, @Err2=@Error OUTPUT

The @SQL Variable ends up being:

EXEC spExample1 000000, 111111, @Err=@Error OUTPUT

^^Which works perfectly by itself.

Basically I'm trying to get the OUTPUT through the above code, but when it's ran through Dynamically.

Is this possible or is there another way to do this?

The way things kind of play out in the end appear to be:

EXEC sp_executesql EXEC spExample1 000000, 111111, @Err=@Error OUTPUT, @Err2 nVARCHAR(1) OUTPUT, @Err2=@Error OUTPUT

After looking at that it looks ridiculous, however any help would definitely be appreciated.

Solution

  • The following code works perfectly (For N.. output and input parameters) please try this (source):

    CREATE PROCEDURE Myproc
@parm varchar(10),

@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT
AS
SELECT @parm1OUT='parm 1' + @parm
SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
@parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
@parm1OUT varchar(30) OUTPUT,
@parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@parm=@parmIN,
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc