CODEWITHSUNDEEP
sql-server
Muirik
Muirik

Reputation: 6289

CASTING to INT Problem in SQL Server stored procedure

I am running into an error with a stored procedure I'm trying to run in SQL Server. Part of my procedure looks like this:

    WHERE 
        RM20101.CUSTNMBR NOT LIKE ('CR0002%')
        AND RM20101.VOIDSTTS != 1
        AND CAST(REPLACE(LEFT( RM20101.DOCNUMBR, CHARINDEX('-', RM20101.DOCNUMBR)), '-', '') AS INT) = 481492) a
WHERE 
    a.DocumentAmount != 0

The CAST I'm doing here results in this error:

The conversion of the varchar value '02570000096' overflowed an int column.

So I tried using BIGINT instead, like so:

    WHERE 
        RM20101.CUSTNMBR NOT LIKE ('CR0002%')
        AND RM20101.VOIDSTTS != 1
        AND CAST(REPLACE(LEFT( RM20101.DOCNUMBR, CHARINDEX('-', RM20101.DOCNUMBR)), '-', '') AS BIGINT) = 481492) a
WHERE 
    a.DocumentAmount != 0

But that produces this error:

Error converting data type varchar to bigint.

How can I handle this in a way that won't error out?

Upvotes: 0

Views: 165

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Since you deleted your last question.

I suspect you have some bogus or unanticipated data in DOCNUMBR

If you use try_convert(), it will return a NULL value rather than throwing an error

Declare @YourTable table (DOCNUMBR varchar(50))
Insert Into @YourTable values
 ('004000009123-000')
,('02570000096-000')
,('BOGUS-STRING')
,('BOGUS')
,(null)


Select *
      ,AsAnInt = try_convert(bigint,left(DOCNUMBR,charindex('-',DOCNUMBR+'-')-1))
 From  @YourTable

Returns

DOCNUMBR            AsAnInt
004000009123-000    4000009123
02570000096-000     2570000096
BOGUS-STRING        NULL         -- Fails to Convert but no error
BOGUS               NULL         -- Fails to Convert but no error
NULL                NULL         -- Fails to Convert but no error

To find the Trouble Records

Select *
 From  @YourTable
 Where try_convert(bigint,left(DOCNUMBR,charindex('-',DOCNUMBR+'-')-1)) is null

Returns

DOCNUMBR
BOGUS-STRING
BOGUS
NULL

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

One or more of your rows contains a value that doesn't convert. Consider using TRY_CONVERT instead or filter the bad rows out with a where clause

You could also consider not converting the data at all and just look for WHERE column LIKE '481492-%' or similar. Not converting means you have more chance of using an index

Upvotes: 1

Related Questions