Back to Blog
![]() ![]() It's a lot shorter than the CASE statement. (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%%') This error occurs when converting a numeric or decimal value into a varchar data type, either implicitly or explicitly, and the length of the varchar variable. (I've noticed that since learning CROSS/OUTER APPLY I've improved my query capability with older SQL Server versions, too-as I am getting more versatile with the "outer reference" capabilities of SELECT, ON, and WHERE clauses!) SELECT ![]() An alternate to the "traditional way" that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. There are additional issues here but I can't address them now. You may be interested to read Erland Sommarskog's Microsoft Connect request about this evaluation order issue. By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%%'Ĭonvert(bigint, X.Account_Code) BETWEEN 503105 ![]() However, I like using strategies such as this with SQL Server 2005 and up: SELECTĪccount_Code = Convert(bigint, X.Account_Code), Something like this: SELECTīigint, - only gives up to 18 digits, so use decimal(20, 0) if you must The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type if an error occurs, null is returned. TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. ![]()
0 Comments
Read More
Leave a Reply. |