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
– Giorgi Nakeuri, Commented Jun 13, 2015 at 10:24

SQL Police · Accepted Answer · 2015-06-13 11:15:55Z

5

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

answered Jun 13, 2015 at 11:15

SQL Police

4,1961 gold badge28 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2021-12-04 18:37:08Z

0

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

answered Dec 4, 2021 at 18:37

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Calling stored procedure with OUTPUT parameter in dynamic SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related