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

Answer 1

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

Answer 2

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