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.

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
Possible duplicate of [How to get sp\_executesql result into a variable?](http://stackoverflow.com/questions/803211/how-to-get-sp-executesql-result-into-a-variable) — , Mar 23 '16 at 16:42

score 3 · Accepted Answer · edited Jul 11 '23 at 21:42

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

score 0 · Answer 2 · answered Oct 27 '10 at 20:49

0

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.

answered Oct 27 '10 at 20:49

Philip Kelley

39,426
11
57
92

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
1

If you still remember the anwer, could you please post it and mark it as an answer? – arviman Nov 02 '11 at 00:13

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

2 Answers2