Why this code give one row with NULL value instead of error message Must declare the scalar variable "@i".. And why Microsoft used this behavior in t-sql?
if 1 = 0
begin
declare @i int = 1;
end;
select @i;
Asked
Active
Viewed 765 times
2
Ruslan K.
- 1,912
- 1
- 15
- 18
-
1`1 = 0` ? Also, `declare @i = 1` – Gurwinder Singh Dec 26 '16 at 09:38
-
Double typo: `1 = 0` gets you inside the `if`, and the incorrect assignment declares `@i` but does not assign a value. – Tim Biegeleisen Dec 26 '16 at 09:40
-
Please, try to execute this code in SSMS. It is correct. `declare` doesn't executed. – Ruslan K. Dec 26 '16 at 09:43
-
Possible duplicate of [Why is variable declared inside IF statement created even when condition evaluates to false?](http://stackoverflow.com/questions/2952553/why-is-variable-declared-inside-if-statement-created-even-when-condition-evaluat) – Munavvar Dec 26 '16 at 10:02
2 Answers
4
From MSDN:
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.
so in your example the scope of @i variable is the batch or procedure where they are defined.
so the next query:-
if 1 = 0
begin
declare @i int = 1
end
else
begin
declare @i int = 3
end
select @i
is retrieving the next error:-
The variable name '@i' has already been declared. Variable names must be unique within a query batch or stored procedure.
but your query does not.
Update
The Microsoft has said they won't fix this:-
Make it possible to declare variables that are only visible within a block.
ahmed abdelqader
- 3,409
- 17
- 36
3
DECLAREs scope is current batch, not code block.
So it's ok that compiler sees your variable.
The question is - why is it not assigned if it's visible. The assignment is the same as regular assigment, just written in-line in variable declaration. And assignments are commands that are affected by code flow. Here is actual execution plan for your code:
<Batch>
<Statements>
<StmtCond StatementCompId="1" StatementId="1" StatementText="
if 1 = 0
" StatementType="COND" RetrievedFromCache="false">
<Condition />
<Then>
<Statements>
<StmtSimple StatementCompId="2" StatementId="2" StatementText="
begin
 declare @i int = 1;
" StatementType="ASSIGN" RetrievedFromCache="false" />
</Statements>
</Then>
</StmtCond>
</Statements>
<Statements>
<StmtSimple StatementCompId="4" StatementId="3" StatementText="
end;

select @i;
" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="false" />
</Statements>
</Batch>
and below is the plan for expanded declaration and assignment:
declare @i int
if 1 = 0
begin
set @i = 1;
end;
select @i;
<Batch>
<Statements>
<StmtCond StatementCompId="1" StatementId="1" StatementText="declare @i int 

if 1 = 0
" StatementType="COND" RetrievedFromCache="false">
<Condition />
<Then>
<Statements>
<StmtSimple StatementCompId="2" StatementId="2" StatementText="
begin
 set @i = 1;
" StatementType="ASSIGN" RetrievedFromCache="false" />
</Statements>
</Then>
</StmtCond>
</Statements>
<Statements>
<StmtSimple StatementCompId="4" StatementId="3" StatementText="
end;

select @i;
" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="false" />
</Statements>
</Batch>
Ivan Starostin
- 8,798
- 5
- 21
- 39