CODEWITHSUNDEEP
sql
palak mehta
palak mehta

Reputation: 706

Error in Converting nvarchar to int

I have one table #DateDay_temp in which there is a column ExtraAdultPrice of type NVARCHAR(20). When I am trying to run this statement in a stored procedure

DECLARE @ExtraAdultPAX DECIMAL(10,2)

SELECT 
    @ExtraAdultPAX = SUM(CAST((CASE ISNULL(ExtraAdultPrice, '') 
                                  WHEN '' THEN 0 
                                  ELSE ExtraAdultPrice END) AS DECIMAL(10,2))) 
FROM #DateDay_temp;

When I am passing the value 54.56 in the ExtraAdultPrice" column, I get the error

Conversion failed when converting the nvarchar value '54.56' to data type int.

Help would be appreciated.

Upvotes: 0

Views: 195

Answers (2)

marc_s
marc_s

Reputation: 754278

Most likely this error happens because of the fact you have a 0 in your CASE statement; therefore, the "type" of the CASE statement is decided to be an INT - which is completely wrong here....

Try this instead:

SELECT 
    @ExtraAdultPAX = SUM(CAST((CASE ISNULL(ExtraAdultPrice, '') 
                                 WHEN '' THEN 0.00   <<== use "0.00" instead of just "0" !!
                                 ELSE ExtraAdultPrice 
                               END) AS DECIMAL(10,2))) 
FROM 
    #DateDay_temp;

Now, everything should be DECIMAL(10,2) and there shouldn't be any attempt to convert your values to an INT

Upvotes: 2

Dave Hogan
Dave Hogan

Reputation: 3221

54.56 isn't an int because of the decimal point.

Upvotes: 0

Related Questions