CODEWITHSUNDEEP
sqlsql-server-2008
user793468
user793468

Reputation: 4976

checking for invalid values sql

I have a stored procedure where I have a condition to check whether a Rating Code is 1,2 or 3 in the where clause. Something like this:

WHERE
CONVERT(INT, LEFT(RatingCode, 1)) IN (1,2,3) AND

At times when there are bad values in RatingCode column the above line throws an error. Hence I came up with the below solution:

WHERE
CASE WHEN ISNUMERIC(LEFT(RatingCode, 1)) = 1 
    THEN CASE WHEN CONVERT(INT, LEFT(RatingCode, 1)) IN (1,2,3) 
            THEN 1
            ELSE 0
        END
    ELSE 0
END = 1 AND

Here if there is an invalid value(non numeric) in RatingCode column then I want to ignore that record. Is my above solution a good one? Or is there any better solution?

Upvotes: 0

Views: 619

Answers (1)

KekuSemau
KekuSemau

Reputation: 6852

In that specific case, you could also just use

WHERE
LEFT(RatingCode, 1) IN ('1','2','3') AND

Besides that, also string comparisons are allowed in tsql.

WHERE
LEFT(RatingCode, 1) BETWEEN '1' AND '3' AND

This does not throw an error for non-numeric letters.

Upvotes: 2

Related Questions