Search code examples
sql-servert-sqldebuggingsqldatatypes

SQL Server 2005 - comparing char column with int

I came across an interesting stored procedure and I need your help to figure this one out.

Basically, the stored procedure SELECTs with WHERE condition where the condition is:

WHERE SomeType = 2

SomeType is a char(3) column, which contains values like '1','2','AA','AB' etc.

When I run the stored procedure in SSMS, it fails with:

Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'AA' to a column of data type int.

However, when I use that stored procedure through an app in Production, it returns data without any issues.

My question is, how is that possible?

Solution

  • You say in the comments that you have additional WHERE clauses not shown.

    The issue may be different execution plans. e.g. suppose your real WHERE condition is

    WHERE SomeType = 2 AND Foo = 'X'

    and your data is

    SomeType Foo
-------- ----
1        X
2        X
AA       Y
AB       Y

    Then on one instance it might evaluate Foo = 'X' first leaving rows

    SomeType Foo
-------- ----
1        X
2        X

    Then it runs the IMPLICIT_CAST(SomeType AS INT) = 2 condition against these filtered rows with no problems.

    However if the order is reversed such that it first evaluates the SomeType = 2 condition then it will need to cast the values AA and AB to int and the query will fail.