Weird CAST/TRY_CAST Behaviour

Question

Weird CAST/TRY_CAST Behaviour

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100946 More actions · Answer 1

Thom A wrote:

kuopaz wrote:
Out of interest - by 'later', are you saying that the CAST/ TRY_CAST is being executed in the SELECT before the WHERE?
That shouldn't be possible. Per Logical Processing Order of the SELECT statement, the SELECT is processed after the WHERE, JOIN, ON, etc clauses:

<li style="list-style-type: none;">
FROM

<li style="list-style-type: none;">
ON

<li style="list-style-type: none;">
JOIN

<li style="list-style-type: none;">
WHERE

<li style="list-style-type: none;">
GROUP BY

<li style="list-style-type: none;">
WITH CUBE or WITH ROLLUP

<li style="list-style-type: none;">
HAVING

<li style="list-style-type: none;">
SELECT

<li style="list-style-type: none;">
DISTINCT

<li style="list-style-type: none;">
ORDER BY

<li style="list-style-type: none;">
TOP

In my scenario, the reason was because it was the function's SELECT that was generating the error, which was using data that would have been filtered out elsewhere. When running the SQL outside of the SP, then the order of processing completed the filtering first, where as when the SP itself was run, the filtering happened after, and so the error occured.

That sequence is not 100% fixed. SQL may, and sometimes does, do certain things in a different order.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

kuopaz Default port Points: 1449 More actions · Answer 2

Yes, order of processing can differ - rare and not something I had seen before.

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15

Yellow box warning following the section Logical Processing Order of the SELECT statement:

"The preceding sequence is usually true. However, there are uncommon cases where the sequence may differ.

For example, suppose you have a clustered index on a view, and the view excludes some table rows, and the view's SELECT column list uses a CONVERT that changes a data type from varchar to integer. In this situation, the CONVERT may execute before the WHERE clause executes. Uncommon indeed. Often there is a way to modify your view to avoid the different sequence, if it matters in your case."

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 3

Just in case someone comes here with a similar problem, the article below has an explanation of a possible scenario and the solution.

TRY_CAST Arithmetic Overflow Error - Purple Frog Systems

https://www.purplefrogsystems.com/2024/03/try_cast-arithmetic-overflow-error/?utm_source=rss&utm_medium=rss&utm_campaign=try_cast-arithmetic-overflow-error