Calling stored procedure with OUTPUT parameter in dynamic SQL

Question

I am calling a stored procedure with OUTPUT parameter using dynamic SQL.

set @cSql='EXEC '+@cName+'.dbo.uspNDateGet '''
    +convert(varchar(10),@dtAsOn,102)+''','''
    +@cBr+''','''
    +@cLCode+''','''
    +convert(varchar(10),@dtNDate,102)+''' OUTPUT'

exec(@cSql)

On executing the script, I get following error.

Cannot use the OUTPUT option when passing a constant to a stored procedure.

Without using dynamic SQL, the script gives me the required result.

EXEC uspNDateGet @dtAsOn,@cBr,@cLCode,@dtNDate OUTPUT

Declare that output variable inside dynamic query. Use sp_executesql with passing parameters. Look for output from dynamic sql — Giorgi Nakeuri, Jun 13 '15 at 10:24

score 5 · Answer 1 · answered Jun 13 '15 at 11:15

You need to pass parameters from outside into the inside query.

Here I show you the generic case:

declare @sql nvarchar(max);

declare @Out1 nvarchar(10);
declare @Out2 nvarchar(10);

declare @ParmDef nvarchar(max);

set @ParmDef = 
      '  @Parm_Out1 nvarchar(10) '
    + ', @Parm_Out2 nvarchar(10) ' ;

set @sql='EXEC myproc @Parm_Out1 OUTPUT, @Parm_Out2 OUTPUT '

exec sp_executesql @sql, @ParmDef, @Parm_Out1 = @Out1, @Parm_Out2 = @Out2

score 0 · Answer 2 · answered Dec 04 '21 at 18:37

In this particular instance, you don't actually need dynamic SQL at all.

You can parameterize the name of the stored procedure being called with EXEC, and pass the parameters normally. This is documented here:

DECLARE @dtNDate datetime, @procName nvarchar(386);

SET @ProcName = @cName + N'.dbo.uspNDateGet';

EXEC @procName
  @dtAsOn = @dtAsOn,
  @cBr = @cBr,
  @cLCode = @cLCode,
  @dtNDate = @dtNDate OUTPUT

Calling stored procedure with OUTPUT parameter in dynamic SQL

2 Answers2