sql server - SQL assign variable with subquery -
i have question following 2 sql:
declare @i1 bit, @b1 bit declare @i2 bit, @b2 bit declare @t table (seq int) insert @t values (1) -- verify data select case when (select count(1) @t n2 1 = 2) > 0 1 else 0 end -- result 0 select @i1 = 1, @b1 = case when @i1 = 1 or ((select count(1) @t n2 1 = 2) > 0) 1 else 0 end @t n n.seq = 1 select @i1, @b1 -- result 1, 0 select @i2 = 1, @b2 = case when @i2 = 1 or (0 > 0) 1 else 0 end @t n n.seq = 1 select @i2, @b2 -- result 1, 1
before execute, thought case part should null = 1 or (0 > 0)
, , return 0
.
but now, wondering why 2nd sql return 1
i post answer quite large text training kit (70-461)
:
where propertytype = 'int' , cast(propertyval int) > 10
some assume unless precedence rules dictate otherwise, predicates evaluated left right, , short circuiting take place when possible. in other words, if first predicate propertytype = 'int' evaluates false, sql server won’t evaluate second predicate cast(propertyval int) > 10 because result known. based on assumption, expectation query should never fail trying convert isn’t convertible.
the reality, though, different. sql server internally support short-circuit concept; however, due all-at-once concept in language, not going evaluate expressions in left-to-right order. decide, based on cost-related reasons, start second expression, , if second expression evaluates true, evaluate first expression well. means if there rows in table propertytype different 'int', , in rows propertyval isn’t convertible int, query can fail due conversion error.
Comments
Post a Comment