Arithmetic overflow error converting nvarchar to data type numeric

Transact-SQL
1 
--execute [dbo].[SC_RefreezeCheckList] 1,'MI1010A443',1
ALTER Procedure [dbo].[SC_RefreezeCheckList] 
 @location as int=null
,@Part_Number as nvarchar(50)=null
,@Branch_Id as int =null
As
Begin

declare @query nvarchar(max)

set @query= 'SELECT BinStockQuan'+cast(@location as nvarchar(4)) +' From SM_StockFile where Part_Number = '''+@Part_Number+''' AND Branch_ID = '''+CONVERT(nvarchar,@Branch_Id)+''''
 exec (@query)
 update SC_StockCheckFile  set ReFreezeDate = GETDATE (),ReFreezeTime = convert(time,getdate()) 
   ,FrozenQuantity =cast(@query as decimal(18,2)) from SC_StockCheckFile where Location =@location 
end
2

@query is your select statement, and you're trying to cast it as a decimal value?

1 Like
3

Yes, when i exec @query respose is like 3.00, how to cast it to decimal value?

4

Ahh I see; you're trying to cast the result of the select. That's not what the code does. I would like to suggest a different way of updating, but before I can do that, I need you to provide:

  • table definitions (of both tables) in the form of create statements
  • sample data in the form of insert statements (so I can validate the code I will be suggesting)
  • expected output from the sample data you provide

If I were to join the two tables, which fields would I use to get a one-to-one relation?

5

declare @query decimal(18,2) = '2310.45'

select cast(@query as decimal)

image

hope this is what you are looking for ??

:slight_smile:
:slight_smile:

6

The workaround that I use for this is converting the value to float and then numeric.