CODEWITHSUNDEEP
sqlsql-server-2008casting
Brandon Moore
Brandon Moore

Reputation: 8780

Why does this cast work in one statement and not another?

These two queries work fine:

select cast(sku as bigint) from bid                 
select cast(sku as bigint) from vwbidrecords

This query fails with the message "Error converting data type varchar to bigint."

select * from bid where cast(sku as bigint) in  
(select cast(sku as bigint) from vwbidrecords)

Does it make sense to anyone why the 3rd wouldn't work even though the 1st two are fine?

Upvotes: 1

Views: 215

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

You're using a view, which I am guessing must have some multi-table JOIN involved (that filters the view to only numeric SKUs) or at least a single table with a WHERE clause.

It will also most likely involve some ISNUMERIC test in the view query.

Check out this bug SQL Server should not raise illogical errors which explains why

Error converting data type varchar to bigint.

will be raised when you least expect it. The fact that

select cast(sku as bigint) from vwbidrecords

on its own works only means that it is optimized differently to query 3. Check both execution plans and it will become clearer.

Upvotes: 3

Related Questions