Why does the sql select statement fail?

Question

In the scenario below, the final select from the Combine view fails, any ideas why?

The Subset table does not have a row that corresponds to the one in MasterCodes that wouldn't cast to an integer value.

CREATE TABLE MasterCodes (
  ID INT
, Code VARCHAR(10) )
GO

CREATE TABLE Subset (
  ID INT )
GO


CREATE VIEW Combine AS
SELECT S.ID
, M.Code
, CAST(M.Code AS INT) IntCode
FROM Subset S
INNER JOIN MasterCodes M ON M.ID = S.ID
GO

INSERT MasterCodes (ID, Code) VALUES (1, '1')
INSERT MasterCodes (ID, Code) VALUES (2, '2')
INSERT MasterCodes (ID, Code) VALUES (3, 'three')
INSERT MasterCodes (ID, Code) VALUES (4, '4')
INSERT Subset (ID) VALUES (1)
INSERT Subset (ID) VALUES (2)
INSERT Subset (ID) VALUES (4)


SELECT * FROM Combine -- 3 rows returned

SELECT * FROM Combine WHERE Code = '2' -- 1 row returned

SELECT * FROM Combine WHERE Code = '3' -- 0 rows returned

SELECT * FROM Combine WHERE IntCode = 2 -- fails, error msg is

Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value 'three' to data type int.

Environment is Sql2k5 Standard (64-bit) ON Win2k3 Server R2

Answer 1

Probably because in some row, M.Code contains the literal word "three", and the view is trying to cast a non-numeric looking word into an int (you can probably cast "3" into an int, but not "puppy" or "three", etc.).

Edit: Added a comment, but worthwhile to add it here. SQL Server is going to try and execute and join the as efficiently as possible, and it's going try and apply the where clause apparently even before joining.

This makes sense if you consider that VIEWs nowadays work almost fully like a real table. It has to do something SIMILAR to this; otherwise, it will join everything and return all values BEFORE being filtered out.

Hideously expensive.

What I'm not sure about is if the execution plan will show this level of detail.

Answer 2

You're inserting the string "three" into MasterCodes.Code, and your view is attempting to cast this value to an integer. This SQL should give the same error:

select cast("three" as int)

Solution? Replace "three" with "3" in your insert statement.

Answer 3

Because when you try to use IntCode in your condition logic the view try's to cast "three" as an int.

Use isnumeric() with a case statement to create the view

case when isnumeric(field) then
    cast(field as int)
else
    null
end AS IntCode

Answer 4

If your result set is accurate: "SELECT * FROM Combine WHERE Code = '2' -- 1 row returned SELECT * FROM Combine WHERE Code = '3' -- 0 rows returned SELECT * FROM Combine WHERE IntCode = 2 -- fails, error msg isMsg 245, Level 16, State 1, Line 15Conversion failed when converting the varchar value 'three' to data type int."

then the only time it fails is when you try to compare against IntCode field, it almost seems like it is failing when it tries to put the non-numeric value on the left side of the "IntCode = 2" comparison, because this is the only time it will need to pick up every single value in the code field.

Hope that helps!