Error returning value from dynamic sql

Transact-SQL
1

I have the following piece of code that intends to return a value from a dynamics sql:

DECLARE @SQLStatement NVARCHAR(max),
@ReturnValue NUMERIC(12, 2)

SET @SQLStatement = 'select @ReturnValue = 1864.87'
SET @ReturnValue = 0.00

EXEC Sp_executesql
@SQLStatement,
N'@ReturnValue NUMERIC(12, 2) OUTPUT',
@ReturnValue = OUTPUT

PRINT @ReturnValue

when i run it, i receive the following error message:

Msg 8114, Level 16, State 5, Line 0
Error converting data type nvarchar to numeric.
0.00

but if I run the sql statement directly, it runs fine:

select @ReturnValue = 1864.87

PRINT @ReturnValue

1864.87

I have tried changing the type of the output variable to nvarchar and even to sql_variant but I don´t get any returned value.

What am I doing wrong ?

Thanks in advance for any help.

Nelson

2

either you specify the parameter explicitly

EXEC sp_executesql
      @SQLStatement,
      N'@ReturnValue NUMERIC(12, 2) OUTPUT',
      @ReturnValue = @ReturnValue OUTPUT

OR without the equal sign =

EXEC sp_executesql
      @SQLStatement,
      N'@ReturnValue NUMERIC(12, 2) OUTPUT',
      @ReturnValue OUTPUT
1 Like
3

Thanks a lot for your response. It worked !!! :yum: