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. — Thom A, Aug 07 '19 at 18:43
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. — Thom A, Aug 07 '19 at 18:46
@Larnu There is a lot of code, what would you need exactly? I tried to include the relevant bits, my bad. — Joey C, Aug 07 '19 at 18:47
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. — Thom A, Aug 07 '19 at 18:49
@SeanLange The **ELSE 0** in only in the third example because it requires a int there. Everywhere else needs a '0'. I don't think that's where the problem is. — Joey C, Aug 07 '19 at 18:49
I would just modify your case statement to `WHEN wtpc.Reg_No IS NOT NULL THEN CAST(wtpc.Shipment_Net_Weight AS VARCHAR(??)` where ?? is going to be the max number of digits you would expect. — Brandon Pratt, Aug 07 '19 at 18:51
@Larnu I tried a simple CONVERT(varchar(15), wtpc.Shipment_Net_Weight) but it gave me the error as example 2. — Joey C, Aug 07 '19 at 18:52
Then we need your full statement, we can't replicate your problem with snippets that don't generate the error you describe. — Thom A, Aug 07 '19 at 18:54
As you can see, `CONVERT` does not generate the error: [DB<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=06d812e66d8afd4f5cc099c4c45f12b8), so we're missing (a lot). Also *`'string_alias' = expression`* has been deprecated for years (possibly as far back as 2008). You should be using one of the other methods. — Thom A, Aug 07 '19 at 18:58
@Larnu Thank you for the help, trying to not span too much code. But I edited the post with more code. Hope it helps. — Joey C, Aug 07 '19 at 18:59
I can only *guess* that you aren't converting something that isn't a `varchar` to a `varchar`. We can't use that SQL to replicate the problem, as it's incomplete, and we have no data. If you can provide a way to replicate the problem (SQL that doesn't work, and sample data, like I did above) we can help you; otherwise there's little we can do other than guess. — Thom A, Aug 07 '19 at 19:02
I provided my guess. This can be painful to figure out for sure. — Sean Lange, Aug 07 '19 at 19:07
@Larnu Thank you for your patience and help, I am working on a DB that I can replicate the problem with. I'll try to make it as complete as possible. — Joey C, Aug 07 '19 at 19:08
@Larnu Found a fix, thank you very much for your effort helping, I appreciate it a lot! — Joey C, Aug 07 '19 at 19:59

score 0 · Answer 1 · answered Aug 07 '19 at 19:59

0

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!

answered Aug 07 '19 at 19:59

Joey C

59
2
12

Oftentimes in SQL, rows that would get excluded from another part of the WHERE clause will trigger these errors. I've also seen CASE statements do this - the first result returned by the CASE statement will 'set the expected type' of the statement to the query analyser. The quickest thing you can do is reorder the CASE statements. – Broam Aug 07 '19 at 20:10

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 Answers1