Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
CREATE TABLE #t ( propertytype VARCHAR(20), propertyval VARCHAR(20) ) GO INSERT #t(propertytype, propertyval) VALUES ('INT', '5') , ('INT', '15' ) , ('STRING', 'ABC') , ('DATE', '2/20/2015') GO SELECT * FROM #t WHERE propertytype = 'INT' AND TRY_CAST(propertyval AS INT) > 10 GO SELECT * FROM #t WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10 GO SELECT * FROM #t WHERE propertytype = 'DATE' AND CAST(propertyval AS DATETIME) = '2/20/2015' GO SELECT * FROM #t WHERE propertytype = 'DATE' AND TRY_CAST(propertyval AS DATETIME) = '2/20/2015' GO SELECT * FROM #t WHERE propertytype = 'STRING' AND CAST(propertyval AS varchar(10)) = 'ABC' GO SELECT * FROM #t WHERE propertytype = 'STRING' AND TRY_CAST(propertyval AS varchar(10)) = 'ABC'
For example, consider the following filter predicate. WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10 Suppose that the table being queried holds different property values. The propertytype column represents the type of the property (an INT, a DATE, and so on), and the propertyval column holds the value in a character string. When propertytype is 'INT', the value in propertyval is convertible to INT; otherwise, not necessarily. Some assume that unless precedence rules dictate otherwise, predicates will be evaluated from left to right, and that short circuiting will take place when possible. In other words, if the first predicate propertytype = 'INT' evaluates to false, SQL Server won’t evaluate the second predicate CAST(propertyval AS INT) > 10 because the result is already known. Based on this assumption, the expectation is that the query should never fail trying to convert something that isn’t convertible. The reality, though, is different. SQL Server does internally support a short-circuit concept; however, due to the all-at-once concept in the language, it is not necessarily going to evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons, to start with the second expression, and then if the second expression evaluates to true, to evaluate the first expression as well. This means that if there are rows in the table where propertytype is different than 'INT', and in those rows propertyval isn’t convertible to INT, the query can fail due to a conversion error. 68 Chapter 3 Filtering and Sorting Data You can deal with this problem in a number of ways. A simple option is to use the TRY_CAST function instead of CAST. When the input expression isn’t convertible to the target type, TRY_CAST returns a NULL instead of failing. And comparing a NULL to anything yields unknown. Eventually, you will get the correct result, without allowing the query to fail. So your WHERE clause should be revised like the following. WHERE propertytype = 'INT' AND TRY_CAST(propertyval AS INT) > 10
Add your voice to the tech community where 5M+ people just like you are talking about what matters.