SQL assign variable with subquery

Question

I have a question for following 2 SQL:

declare @i1 bit, @b1 bit
declare @i2 bit, @b2 bit
declare @t table (Seq int)
insert into @t values (1)

-- verify data
select case when (select count(1) from @t n2 where 1 = 2) > 0 then 1 else 0 end
-- result 0

select @i1 = 1, @b1 = case when @i1 = 1 or ((select count(1) from @t n2 where 1 = 2) > 0) then 1 else 0 end from @t n where n.Seq = 1
select @i1, @b1
-- result 1, 0

select @i2 = 1, @b2 = case when @i2 = 1 or (0 > 0) then 1 else 0 end from @t n where n.Seq = 1
select @i2, @b2
-- result 1, 1

SQL Fiddle Here

Before the execute, I thought the case part should be null = 1 or (0 > 0), and it will return 0.

But now, I wondering why the 2nd SQL will return 1

Order of variables. Check [demo](http://rextester.com/JME74246). When you have `select @b2 = case when @i2 = 1 or (0 > 0) then 1 else 0 end ,@i2 = 1` you will get 1,0 — Lukasz Szozda, Jul 28 '17 at 07:19
I am more interested in the difference between these two: `select @i1 = 1, @b1 = (case when @i1 = 1 or ((select count(1) from @t n2 where 1 = 2) > 0) then 1 else 0 end) from @t n where n.Seq = 1 select @i1, @b1 -- result 1, 0 select @i1 = 1, @b1 = (case when @i1 = 1 then 1 else 0 end) from @t n where n.Seq = 1 select @i1, @b1` — Giorgi Nakeuri, Jul 28 '17 at 07:22
Thanks the demo, could you explain more? The variable assigned before the `case`, then it should be `1 = 1 or (0 > 0)`. But result of 2 SQL are different — Prisoner, Jul 28 '17 at 07:24
Ok, I see your point. Intresting case :) Looks like some sort of short-circuit evaluation. I am quite sure that I saw similiar case in some blog. — Lukasz Szozda, Jul 28 '17 at 07:25
I remember something about calcs in select and reusing variables. Let me have a look. That was something about all variables are set all at once and you cannot do that like you are doing it now. — Giorgi Nakeuri, Jul 28 '17 at 07:27
[The order of evaluation isn’t defined](https://stackoverflow.com/questions/14883930/order-of-execution-in-sql-server-variable-assignment-using-select), so I guess SQL Server just picks a different one with the addition of the subquery. — Ry-, Jul 28 '17 at 07:31
[Dirty Secrets of the CASE Expression](https://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression) - `CASE will not always short circuit` part + links to ms connect. — Lukasz Szozda, Jul 28 '17 at 08:09
@lad2025 could you put that in summary to answer? Based on [Aggregates Don't Follow the Semantics Of CASE](https://connect.microsoft.com/SQLServer/feedback/details/691535/aggregates-dont-follow-the-semantics-of-case) and [CASE / COALESCE won't always evaluate in textual order](https://connect.microsoft.com/SQLServer/feedback/details/690017/case-coalesce-wont-always-evaluate-in-textual-order), SQL will work on aggregate function first, as _Remark_ section in [Case](https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql). Seems that is the similar case for this. — Prisoner, Jul 28 '17 at 08:12

Paweł Dyl · Answer 1 · 2017-07-28T07:45:59.953

6

Just to extend @Giorgi's answer:

See this execution plan: Since @i2 is evaluated first (@i2=1), case when @i2 = 1 or anything returns 1.

See also this msdn entry: https://msdn.microsoft.com/en-us/library/ms187953.aspx and Caution section

If there are multiple assignment clauses in a single SELECT statement, SQL Server does not guarantee the order of evaluation of the expressions. Note that effects are only visible if there are references among the assignments.

It's all related to internal optimization.

edited Jul 28 '17 at 07:45

answered Jul 28 '17 at 07:23

Paweł Dyl

8,888
1
11
27

1

So why isn’t that the case for `@i1`? – Ry- Jul 28 '17 at 07:24
I've extended my answer. – Paweł Dyl Jul 28 '17 at 07:46
Basically, this Caution sentence is whole point and answer to the question. – Nenad Zivkovic Jul 28 '17 at 07:53
Thanks for the info, but seems not in my case. By the execution plan (text will be better in this case), it shows that will execute the `WHERE` first, due to cost-related reason as Giorgi Nakeuri's answer mentioned. – Prisoner Jul 28 '17 at 08:38

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

I will post this as an answer as it is quite large text from Training Kit (70-461):

WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10

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.

Actually I am not sure if this answers your question, but looks like it has something to do with this concept. — Giorgi Nakeuri, Jul 28 '17 at 07:36
Yep, seems it's part of the answer, with "cost-related reasons", it evaluate the `when` statement first (with sub-query), then `case`, and assign `@i` in latest stage. In the order SQL, as all "part" have same "cost", so it evaluate from left to right — Prisoner, Jul 28 '17 at 08:34

score 4 · Answer 3 · answered Jul 28 '17 at 08:20

Just to extend both answers.

From Dirty Secrets of the CASE Expression:

CASE will not always short circuit

The official documentation implies that the entire expression will short-circuit, meaning it will evaluate the expression from left-to-right, and stop evaluating when it hits a match:
The CASE statement  evaluates its conditions sequentially and stops with the
first condition whose condition is satisfied.

And MS Connect:

CASE Transact-SQL

The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible.

SQL assign variable with subquery

3 Answers3

Linked