Scope of variables in the "Go" SQL SERVER stored procedure

Today, I met a very interesting thing that I had not noticed before, so write it down.

Let's look at examples first.

SETAnsi_nulls onGOSETQuoted_identifier onGO CREATE PROCEDUREgetorderbeforedays@BeforDays INT asBEGIN    IF @BeforDays < 0    BEGIN        DECLARE @Today DATETIME        SET @Today = GETDATE()        DECLARE @Date DATETIME        SET @Date = DATEADD( Day,@BeforDays,@Today)SELECT *  fromOrdersWHERE CONVERT(VARCHAR(Ten), OrderDate, the)= CONVERT(VARCHAR(Ten),@Date, the)    END    ELSE    BEGIN    SELECT *  fromOrdersWHERE CONVERT(VARCHAR(Ten), OrderDate, the)= CONVERT(VARCHAR(Ten),@Today, the)    ENDENDGO

This stored procedure does not have any problem when it is created, but the actual execution is incorrect when the parameter is greater than 0 o'clock and cannot get the data for the day. Looking closely you will find that the definition of the @today variable is in the first if statement, but can also be used in the Else statement, which differs from the usage in C #.

Originally in SQL Server, the place where the variable was declared begins at the end of the batch or stored procedure that declares the variable, so the variable defined in the IF statement can also be accessed in the Else statement.

However, this can cause some unnecessary errors. As in the preceding example, although the defined variable can be accessed in the Else statement, it is not assigned a value, so the order is not found on the day of execution.

It is recommended that all variables and initializations be placed at the very beginning of the stored procedure as far as possible, and at a glance you can see which variables are defined and what values are assigned. This prevents unnecessary hassles by accidentally repeating the same variable in a large number of if ELSE statements, and avoids errors like those in the preceding example

1 SETAnsi_nulls on2 3 GO4 5 SETQuoted_identifier on6 7 GO8 9  Ten  One CREATE PROCEDUREgetorderbeforedays A  -     @BeforDays INT -  the  as -  - BEGIN -  +     DECLARE @Today DATETIME -  +     SET @Today = GETDATE() A  at     DECLARE @Date DATETIME -  -     SET @Date = DATEADD( Day,@BeforDays,@Today) -  -     IF @BeforDays < 0 -     BEGIN in     SELECT *  fromOrdersWHERE CONVERT(VARCHAR(Ten), OrderDate, the)= CONVERT(VARCHAR(Ten),@Date, the) -     END to     ELSE +     BEGIN -     SELECT *  fromOrdersWHERE CONVERT(VARCHAR(Ten), OrderDate, the)= CONVERT(VARCHAR(Ten),@Today, the) the     END *  $ ENDPanax Notoginseng  - GO

Scope of variables in the "Go" SQL SERVER stored procedure