SQL cannot convert an int to a varchar(15) value and throws: Conversion failed when converting the varchar value ' LBS' to data type int

Question

I am working on a SQL project that is pulling data from many tables to list them out in an email body. In the body of my email, I am pulling int values that are throwing exceptions because the body needs to eventually be a nvarchar(max). Different types of conversion have not worked for me yet.

This is SQL running on Microsoft SQL Server Management Studio. I have tried leaving the int unconverted, and a few different conversion methods I have seen elsewhere.

In the examples, wtpc.Reg_No is a varchar and wtpc.Shipment_Net_Weight is an int.

'Body' = 
...
+ 'Net:     ' +
    CASE
        WHEN wtpc.Reg_No IS NOT NULL THEN wtpc.Shipment_Net_Weight
        ELSE '0'
    END
    + ' LBS'
+ 'Gross:
...

Results in: Conversion failed when converting the varchar value ' LBS' to data type int.

'Body' = 
...
+ 'Net:     ' +
    CASE
        WHEN wtpc.Reg_No IS NOT NULL THEN str(wtpc.Shipment_Net_Weight)
        ELSE '0'
    END
    + ' LBS'
+ 'Gross:
...

Results in: Error converting data type varchar to float.

'Body' = 
...
+ 'Net:     ' +
    CASE
        ELSE wtpc.Reg_No IS NOT NULL THEN
                                      CONVERT(INT,                               
                                              CASE                                           
                                                   WHEN IsNumeric(CONVERT(VARCHAR(15), wtpc.Shipment_Net_Weight)) = 1 
                                                   THEN CONVERT(VARCHAR(15),wtpc.Shipment_Net_Weight)

                ELSE 0 
         END) 
        ELSE '0'
    END
    + ' LBS'
+ 'Gross:
...

Results in: Conversion failed when converting the varchar value ' LBS' to data type int. This is lifted from this StackOverflow Question

Here is a more complete view:

Declare @TempResults1 table(
ThisSubject nvarchar(max),
ThisTo nvarchar(max),
ThisCC nvarchar(max),
ThisBody nvarchar(max),
ThisBody2 nvarchar(max))

Insert into @TempResults1
Select 

'Subject' = 
isnull( @ShipmentNoString, '') + ' - ' + shpr.Last_Name + ', ' + shpr.First_Name + isnull(' - '  +  @ModeString, '') 
+ case
    when cstm.broker_agent = -1 then ' - Foreign Port Agent Pre-Alert'
    else ' - Port Agent Pre-Alert'
end,

... [To and CC are set here similiar to how the subject is set]
'Body' = 

+ 'To:   ' +  vncb.Vendor_Name
+ 'Attn: ' +
    case
        when @GsaUsContact is not null then @GsaUsContact
        else @GsaForContact
    end
+ 'From: ' + usrs.Name
+ 'Date: ' + format(getdate(), 'ddMMyy')
... [More similar code]
+ 'Mode:    ' + mode.Mode
+ 'Net:     ' +
    case
        when wtpc.Reg_No is not null then CAST( wtpc.Shipment_Net_Weight AS varchar(15))
        else '0'
    end
    + ' LBS'
+ 'Gross:   ' +
    case
        when wtpc.Reg_No is not null then CAST( wtpc.Shipment_Gross as varchar(15))
        else '0'
    end
    + ' 2LBS'
+ 'Cube:    ' + isnull(wtpc.Shipment_CFT, '0') + ' CFT'
+ 'Pieces:  ' +
    case
        when wtpc.Reg_No is not null then CAST(wtpc.Shipment_Piece_Count, varchar(15))
        else '0'
    end
    + ' PCS'
+ '<BR>'

+ 'OBL/AWB #:   ' + asea.Mawb_Obl_No
...[More code like above with varchars]
+isnull('<b>   Please send all correspondence to:  ' + usrs.email + '.</b>', '')

,Body2 = null

We appear to be missing a lot of your SQL, as on it's own that statement isn't valid. The error is telling you the problem here though 'LBS' is not an integer. — Larnu
As wtpc.Shipment_Net_Weight is an int, and int has a higher datatype precedence that a varchar, you end up with wtpc.Shipment_Net_Weight + ' LBC' = 123 + ' LBC' You can't "add" the string ' LBC' to the number 123 and hence the error. You need to convert wtpc.Shipment_Net_Weight to a varchar with an appropriate length. — Larnu
@Larnu There is a lot of code, what would you need exactly? I tried to include the relevant bits, my bad. — Joey C
It was the lack of a leading single quote that really threw me, @JoeyC . I added it, as it was clear that actually the start of the statement is a literal string. If that's assumption wrong, we need to whole statement, if not, my above comment applies. — Larnu

Joey C Joey C · Accepted Answer · 2019-08-07T19:59:16

Found the fix:

isnull( convert(varchar(50), wtpc.Shipment_Net_Weight), '')

There were no matching cases, but the errors didn't show this, kinda goofy! Thank you for all the help everyone!

SQL cannot convert an int to a varchar(15) value and throws: Conversion failed when converting the varchar value ' LBS' to data type int

1 Answers