user1221989 user1221989 - 1 month ago 7
SQL Question

Error converting varchar to numeric error in SQL Server inside CASE statement

I have this SQL :

DECLARE @HTMLROWSSingle NVARCHAR(MAX)
DECLARE @UnrecoveredPercentageOfTotalContract decimal(18,2)
DECLARE @ContractTerm NVARCHAR(20)

SET @ContractTerm = 2
SET @UnrecoveredPercentageOfTotalContract = 1

SET @HTMLROWSSingle = '<tr>' + CASE WHEN @ContractTerm = 2
THEN @UnrecoveredPercentageOfTotalContract * .50
ELSE 1
END + '</tr>'

Select @HTMLROWSSingle


I am getting an error:


Error converting data type varchar to numeric


If I change the SQL to

SET @HTMLROWSSingle = CASE WHEN @ContractTerm = 2
THEN @UnrecoveredPercentageOfTotalContract * .50
ELSE 1
END


I am able to get the data. I am not sure what I am doing wrong here.

Here is the fiddle
http://sqlfiddle.com/#!6/ca0ba/2836


Thanks

Answer Source

You are trying to combine a string with a number. You need to turn the number into a string as well. Try

DECLARE @HTMLROWSSingle NVARCHAR(MAX)
DECLARE @UnrecoveredPercentageOfTotalContract decimal(18,2)
DECLARE @ContractTerm NVARCHAR(20)
SET @ContractTerm = 2
SET  @UnrecoveredPercentageOfTotalContract = 1

SET @HTMLROWSSingle  = '<tr>'+ CAST(Case When @ContractTerm = 2
          Then 
         @UnrecoveredPercentageOfTotalContract  * .50 Else 1 End as nvarchar(50)) +
         '</tr>'

Select  @HTMLROWSSingle