Declare usage in SQL

Usually write SQL query, stored procedures are based on the feeling, did not explore the specific syntax of SQL, has been in accordance with the C # that set to imitate SQL, the first few days in the project encountered a problem caused my interest in the scope of declare definition variables.

We all know about the local variables in C #, if we define a variable in the IF, and his function until the end of if, if the outside is not recognized this variable, else can not be used, simply write it.

If (true)
{
Int32 I = 1;
Console.WriteLine (i);
}

The scope of this I is the IF inside if we use this variable outside of the IF

if (true)
{
Int32 I = 1;
Console.WriteLine (i);
}
Console.WriteLine (i);

The second output statement will be error-

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

The description has gone out of scope for I.

So what happens if we write such a piece of code in SQL? First written in the if

IF1=1
BEGIN
DECLARE@testVARCHAR
SET @test='1'
PRINT 'iN if:'+@test
END

　　Run See results output in If:1 This is the result you can expect. Let's try using the variable @test outside the If .

　　

IF1=1
BEGIN
DECLARE@testVARCHAR
SET@test= 1 ' in if: " + @test
end
print < Span style= "color: #ff0000;" > '

This will be what results, do not know how people think, with my brain homeopathic thought this should be error ah, out of the scope of the variable. The actual results not only did not error but also the @test value.

In If:1

Out If:1

I was depressed when I saw the result, andSQL was a surprise.

In the help document for declare in SQL SERVER 2005 , I found this sentence in the third line of the note, "The scope of a local variable is the batch that it is declared in."

MSDN Address:http://msdn.microsoft.com/zh-cn/library/ms188927.aspx

This line of words in such a large article is really not very impressive.

Now we know that the scope of the original declare variable is where the batch,if blocking its scope, the above our code if both inside and outside the code is in a batch, so @test are available and The value set in if is still there.

Let me change the code, in SQL is a go statement to distinguish between batch processing

IF1=1
BEGIN
DECLARE@testVARCHAR
SET@test=‘1    
   print  '

That's right, after checking the syntax SQL error " must declare a scalar variable " @test ""