Scope of the declare variable in SQL

Usually writeSQLQueries and stored procedures are based on the feeling that they have not been explored.SQLThe specific syntax is always based onC #That same setSQLI encountered a problem in the project a few days ago.DeclareInterest in defining the scope of a variable.

Everyone knowsC #Local variables inIfIf we define a variable, its function isIfEnds,IfDoes not recognize this variable,ElseCan not be used, simply write.

If ( True )
{
Int32 I =   1 ;
Console. writeline (I );
}

ThisIThe scope isIfIf weIfUse this variable outside

If ( True )
{
Int32 I =   1 ;
Console. writeline (I );
}
Console. writeline (I );

 

The second output statement will report an error.

The name 'I' does not exist in the current context

It indicates thatI.

In this caseSQLWrite such a paragraphCodeWhat will happen? First, writeIfInternal 

If   1 = 1
Begin
Declare   @ Test   Varchar
Set   @ Test = ' 1 '
Print   ' I N if: ' + @ Test
End

 

RunView result outputIn if: 1This is the expected result. Then we areIfUse variables outside@ TestTry.

 

If   1 = 1
Begin
Declare   @ Test   Varchar
Set   @ Test = ' 1 '
Print   ' In if: ' + @ Test
End
Print   ' Out if: ' + @ Test

 

What is the result? I don't know what you think. I thought it should be wrong with my brain, and the scope of the variable is out. Not only does the actual result show no error, but also @ Test.

In if: 1

Out If: 1

I was very depressed when I saw this result,SQLUnexpected.

InSQL Server2005In the help documentationDeclareThe third line in the remarks is "The scope of the local variable is the batch processing in which it is declared"

MsdnAddress:Http://msdn.microsoft.com/zh-cn/library/ms188927.aspx

This line of text is really not eye-catching in such a big article.

Now we know the originalDeclareThe scope of the variable is the batch processing,IfThe code above cannot block its scope.IfInternal and external code is in a batch, so@ TestAll are available andIfThe value is still set.

Next I will transform the code,SQLIsGoStatement to differentiate Batch Processing

 

If   1 = 1
Begin
Declare   @ Test   Varchar
Set   @ Test = ' 1 '
Print   ' In if: ' + @ Test
End
Go
Print   ' Out if: ' + @ Test

This is correct, after checking the syntax, the SQL error " scalar variables must be declared " @ test " "