Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL assign variable with subquery

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

like image 313
Prisoner Avatar asked Jul 28 '17 07:07

Prisoner


People also ask

How do you assign a query to a variable in SQL?

When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.

Which keyword is used when assigning a variable from a query?

In below snapshot, SELECT statement is used to assign value to a variable from a select query. The SELECT statement assigns last value from the result set to the variable if the select query returns more than one result set.

Can a subquery be multi valued?

A multi-value subquery retrieves more than one value and has two forms of syntax, as shown below.


1 Answers

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.

like image 97
Giorgi Nakeuri Avatar answered Sep 28 '22 05:09

Giorgi Nakeuri