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

IF 1=1
BEGIN
DECLARE @test VARCHAR
SET @test = ' 1 '
PRINT ' in if: ' [email protected]
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.

　　

IF 1=1
BEGIN
DECLARE @test VARCHAR
SET @test = ' 1 '
PRINT ' in if: ' [email protected]
END
PRINT ' out if: ' [email protected]

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, and SQL was a surprise.

In the Help for SQL SERVER 2005 document about declare, I found this sentence in the third line of the note, "The scope of the local variable is the batch in which it was declared"

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 if the value set is still there.

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

IF 1=1
BEGIN
DECLARE @test VARCHAR
SET @test = ' 1 '
PRINT ' in if: ' [email protected]
END
GO
PRINT ' out if: ' [email protected]

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

Declare usage in SQL