0

I am facing a problem with output from sp_executesql in a stored procedure in SQL Server. Below is the part of the stored procedure:

DECLARE @RET_Amt as NUMERIC(18,2)
DECLARE @v_Select nvarchar(200)
SET @v_select = 'SELECT  @RET_Amt=(14315.00-10420.00) /10420.00'
exec sp_executesql @v_select,N'@RET_Amt numeric output', @RET_Amt output;
SELECT 'Return Value' = @RET_Amt

This returns an output of 0.00 which is not correct. When we just execute select clause on prompt:

select (14315.00-10420.00) / 10420.00

Output is : 0.3738003838

So sp_executesql is rounding the result. Can someone help me to get value without rounding with sp_executesql? My stored procedure is creating the dynamic select clause and building the query. So I have to go with sp_executesql.

Thanks for any input!

TT.
  • 15,774
  • 6
  • 47
  • 88
Rakesh
  • 9
  • 1

1 Answers1

2

You're not properly specifying the precision and scale on your return parameter - if you don't specify anything, the default is numeric(18,0) - which means no digits after the decimal point - hence the rounding.

Try this instead:

DECLARE @RET_Amt as NUMERIC(18,2)
DECLARE @v_Select nvarchar(200)

SET @v_select = 'SELECT  @RET_Amt = (14315.00 - 10420.00) / 10420.00'

EXEC sp_executesql @v_select, N'@RET_Amt NUMERIC(18,2) OUTPUT', @RET_Amt OUTPUT;

SELECT  'Return Value' = @RET_Amt

In the EXEC statement, you need to define the @RET_Amt to be NUMERIC(18,2) (like you defined the variable already) - not just numeric ...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thank you so much Marc. It was great help. – Rakesh Mar 13 '16 at 08:00
  • 2
    @Rakesh: if this answer helped you solve your problem, then please [**accept this answer**](http://meta.stackoverflow.com/q/5234/153998). This will show your appreciation for the people who *spent their own time to help you*. – marc_s Mar 13 '16 at 08:03
  • 1
    @Rakesh To accept an answer, check the ✔ next to the answer. Read more about this etiquette [here](https://stackoverflow.com/help/someone-answers). – TT. Mar 13 '16 at 10:34