>>>>Hi,
>>>>
>>>>I have this stored procedure:
>>>>
>>>>
ALTER PROCEDURE [dbo].[PoliciesGetSuspenseAmount]
>>>>(
>>>> @PolicyHeaderPK uniqueidentifier,
>>>> @SuspenseAmount decimal(18,2) OUTPUT
>>>>)
>>>>AS
>>>> SET NOCOUNT ON;
>>>> SELECT
>>>> @SuspenseAmount = COALESCE(SUM(AmountReceived), 0.00)
>>>> FROM [dbo].[ReceiptDetails]
>>>> inner join [dbo].[Receipts] on ReceiptFK = ReceiptPK
>>>> WHERE
>>>> ([PolicyHeaderFK] = @PolicyHeaderPK
>>>> AND ReceiptTypeFK = 4)
>>>>
>>>>However, when I execute it it rounds my decimals to an integer. If the result should be 69.57 I get back 70.
>>>>
>>>>What have I done wrong?
>>>
>>>What is the type of the AmountReceived column in the database? You can also try to cast to the desired type, e.g.
>>>
>>>coalesce(cast(sum(AmountReceived as decimal (18,2)),0)
>>
>>I tried changing it to this:
>>
>>
SELECT
>> @SuspenseAmount = cast(COALESCE(SUM(AmountReceived), 0.00) as decimal)
>>
>>and that didn't work either. I've now got it returning 70.00 instead of 70, but it should be 69.57.
>
>Switch coalesce and CAST (exactly as I was showing).
>
>Also, post a reproducible scenario and the result of
>
>select @@Version
>
>In additon, always specify precision when casting to decimal!
>
>See this blog post
>
>
Always specify precision with decimal and numericThe problem was the precision in my casting, I had left off the (18,2).
Thanks