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开发者_Go百科 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.

Sorry for the delay :D, 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

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.