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

Question

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.

Answer 1

I haven't tested this in depth, but I did note the following:

When calling stored procedures, you can't mix-and-match named and not-named parameters. So instead of

EXEC sp_executesql @SQL, @ParmDefinition, @Err2=@Error OUTPUT 

use

EXEC sp_executesql @SQL, @ParmDefinition, @Error OUTPUT 

but, since the output parameter defined for sp_executesql is @Err2, it needs to be

EXEC sp_executesql @SQL, @ParmDefinition, @Err2 OUTPUT 

This should work.