CODEWITHSUNDEEP
sqlsql-serverdatabase
Min Lee
Min Lee

Reputation: 377

SQL IF EXISTS behavior weird

I was changing some stored procedure and something weird happened. 

IF EXISTS (SELECT * FROM TABLE1
           WHERE varID = (CAST(@v_varID AS int) + 1))
    UPDATE TABLE2
    SET id = (CAST(@v_varID AS int) + 1) 
    WHERE category = @v_category 
ELSE
    UPDATE CATEGORY 
    SET id = (CAST(@v_varID AS int) + 1) 
    WHERE category = @v_category

This was my code. Because @v_varID is of type varchar, I needed to convert it to int in order to increment id. The problem was when @v_varID is an converted, it get caught at IF EXISTS. When I just update it without EXISTS it works just as I expected.

ERROR_LOG shows another non-convertible @v_varID in the table cannot convert to int. I have been passing just '800' or some varchar-ed integer so it should work but somehow it doesn't pass IF EXISTS (It does update if I get rid of IF EXISTS). I just used another way to make it work but I am curious why.

Thank you!

Upvotes: 1

Views: 92

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Your updates both have the condition category = @v_category.

Your if exists () subquery does not.

I am guessing that varid is a string. The comparison to an integer converts the varid to an integer.

Further, I guess that the category is being checked before the conversion. In other words, there is a row with a bad v_varid value (i.e. cannot be converted to an integer) and this is on a row where the categories do not match.

Upvotes: 2

Related Questions