Take the 2-minute tour ×

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

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

    @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.

share|improve this question
The concept looks sound. What happens when you run it? –  Philip Kelley Oct 27 '10 at 19:06
The OUTPUT value of @Error is NULL, not a 0 or a 1. Yet those are the only 2 options available in the SP: spExample1. –  iamtheratio Oct 27 '10 at 19:10

2 Answers 2

Sorry for the delay :D, the following code works perfectly (For N.. output and input parameters) please try this (source):

@parm varchar(10),

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

EXECUTE sp_executesql
@parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
drop procedure Myproc
share|improve this answer

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, @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.

share|improve this answer
I actually don't think this is possible since you're generating a stored procedure code to be ran through sp_ExecuteSQL so you can't get an OUTPUT of an OUTPUT. Anyway, I found another work around but I really appreciate the help. Thanks! –  iamtheratio Oct 29 '10 at 13:30
If you still remember the anwer, could you please post it and mark it as an answer? –  arviman Nov 2 '11 at 0:13

Your Answer


