CODEWITHSUNDEEP
sql-serverstored-proceduresoutputdynamic-sql
user2944673
user2944673

Reputation: 91

Calling stored procedure with OUTPUT parameter in dynamic SQL

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

Upvotes: 4

Views: 10179

Answers (2)

Charlieface
Charlieface

Reputation: 71579

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

Upvotes: 0

SQL Police
SQL Police

Reputation: 4196

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

Upvotes: 5

Related Questions