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

Question

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

When ContractTerm = 2 should be When ContractTerm = '2' because it is nvarchar not int — GuidoG
– GuidoG, Commented Aug 16, 2017 at 15:39

Element Zero · Accepted Answer · 2017-08-16 16:15:38Z

3

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

edited Aug 16, 2017 at 16:15

answered Aug 16, 2017 at 15:39

Element Zero

1,7613 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

Since the @HTMLROWSSingle variable is of NVARCHAR(MAX) datatype, I'd recommend to use NVARCHAR in the CAST around the CASE, too, to avoid unnecessary implicit conversions

Element Zero Over a year ago

@marc_s I agree - missed that. I updated my answer. Thanks!

Lamak · Accepted Answer · 2017-08-16 15:48:52Z

3

You should always use the right datatypes when using some calculation with them. In this case, you are trying to concatenate a string with a decimal value directly, hence getting a conversion error. You should use something like this instead:

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

SET @HTMLROWSSingle  = '<tr>'+ 
                        Case 
                            When @ContractTerm = N'2'
                            Then CONVERT(NVARCHAR(18),@UnrecoveredPercentageOfTotalContract*.50) 
                            Else N'1' 
                        End +'</tr>';

Select  @HTMLROWSSingle;

edited Aug 16, 2017 at 15:48

answered Aug 16, 2017 at 15:41

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Comments

Rigerta · Accepted Answer · 2017-08-16 15:56:03Z

1

You just need to cast those values into varchar/char, since you are trying to concatenate those values:

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  cast(@UnrecoveredPercentageOfTotalContract  * .50 as nvarchar(10)) Else cast(1 as nvarchar(1)) End + '</tr>'

Select  @HTMLROWSSingle

edited Aug 16, 2017 at 15:56

answered Aug 16, 2017 at 15:39

Rigerta

4,0591 gold badge17 silver badges28 bronze badges

1 Comment

marc_s Over a year ago

Since the @HTMLROWSSingle variable is of NVARCHAR(MAX) datatype, I'd recommend to use NVARCHAR in the CAST around the CASE, too, to avoid unnecessary implicit conversions

Mahan.A · Accepted Answer · 2017-08-16 15:52:48Z

0

Try This:

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 
         TRY_CONVERT(VARCHAR(MAX), @UnrecoveredPercentageOfTotalContract  * .50 )
         ELSE TRY_CONVERT(VARCHAR(2),1)
         END + '</tr>'

Select  @HTMLROWSSingle

answered Aug 16, 2017 at 15:52

Mahan.A

891 silver badge7 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related