CODEWITHSUNDEEP
sql-serversql-server-2008
Brent McKain
Brent McKain

Reputation: 31

SQL Server select behaves differently on table variable from actual table

I have a table with two char columns I care about, name and value. The second column has only two types of strings, those of the form '######' and those of the form '##### / #####'. Obviously, if not for the second data type, the second column should be an int.

Per the comments, here is an example table.

[name]  [value]
ABC1    25
ABC2    13/45
ABC3    12/6
ABC4    15
ABC5    89
XYZ1    89
XYZ2    14
XYZ3    52
XYZ4    13
XYZ5    11

I need to remove all rows from the table that whose name contains 'XY' whose value is less than 50. To determine which rows I need to delete, I could run

SELECT * FROM table WHERE name LIKE 'XY%' AND CONVERT(int,value)<50

which returns

[name]  [value]
XYZ2    14
XYZ4    13
XYZ5    11

However, I'm overly cautious, and prefer to perform delete operations on a table variable first to make sure I didn't overlook anything. If I first insert all of the data from the table into @TempTable, and the run the analagous select statement, the query fails on the row where value = '13/45. Conversion failed when converting the varchar value '13/45' to data type int.

Is there something about a table variable that would cause SQL Server query it differently from a standard table?

Upvotes: 1

Views: 57

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

Since you are on 2014 you can use TRY_PARSE. If a value is unable to be parsed to the specified datatype it will return NULL.

select *
from YourTable
where Name like 'XY%'
    and TRY_PARSE([value] as int) < 50

Upvotes: 1

Related Questions