CODEWITHSUNDEEP
sql-servert-sql
Ruslan K.
Ruslan K.

Reputation: 1981

Access to not declared variable

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;

Upvotes: 2

Views: 846

Answers (2)

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

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.

Upvotes: 4

IVNSTN
IVNSTN

Reputation: 9318

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="&#xD;&#xA;if 1 = 0&#xD;" StatementType="COND" RetrievedFromCache="false">
      <Condition />
      <Then>
        <Statements>
          <StmtSimple StatementCompId="2" StatementId="2" StatementText="&#xA;begin&#xD;&#xA;    declare @i int = 1;&#xD;" StatementType="ASSIGN" RetrievedFromCache="false" />
        </Statements>
      </Then>
    </StmtCond>
  </Statements>
  <Statements>
    <StmtSimple StatementCompId="4" StatementId="3" StatementText="&#xA;end;&#xD;&#xA;&#xD;&#xA;select @i;&#xD;&#xA;" 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 &#xD;&#xA;&#xD;&#xA;if 1 = 0&#xD;" StatementType="COND" RetrievedFromCache="false">
      <Condition />
      <Then>
        <Statements>
          <StmtSimple StatementCompId="2" StatementId="2" StatementText="&#xA;begin&#xD;&#xA;    set @i = 1;&#xD;" StatementType="ASSIGN" RetrievedFromCache="false" />
        </Statements>
      </Then>
    </StmtCond>
  </Statements>
  <Statements>
    <StmtSimple StatementCompId="4" StatementId="3" StatementText="&#xA;end;&#xD;&#xA;&#xD;&#xA;select @i;&#xD;&#xA;" StatementType="SELECT WITHOUT QUERY" RetrievedFromCache="false" />
  </Statements>
</Batch>

Upvotes: 3

Related Questions