Global Variables
Global variables constitute a special type of variable. The server maintains the values in these variables. They carry information specific to the server or a current user session. They can be examined from anywhere, whether from a stored procedure or a batch. In the SQL Server 2005 documentation, Microsoft refers to them as scalar functions, meaning that they return just one value. Since you can still find references to global variables in some documentation and since I would like to use some of them in this chapter, I will review them both here and in the next chapter, which is dedicated to functions.
Global variable names begin with an @@ prefix. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them.
Let's review the principal global variables/scalar functions.

This is a function/global variable that you will use frequently. It is also a feature that generates many of the questions on Usenet newsgroups.
One column in each table can be defined as the Identity column, and the server will automatically generate a unique value in it. This is a standard technique in Microsoft SQL Server for generating surrogate keys (keys whose values are just numbers and do not carry any information). Usually, such columns will be set to assign sequential numbers:

Create table Eq (EqId int identity(1,1),
                 Make varchar(50),

    Requires Free Membership to View

Model varchar(50), EqTypeId int)

The @@identity global variable allows you to get the last identity value generated in the current session. It is important to read the value as soon as possible (that is, in the next Transact-SQL statement). Otherwise, it might happen that you initiate, for example, another stored procedure or a trigger that inserts a record to a different table with an Identity column. In such a case, SQL Server overwrites the number stored in @@identity with the new value. In the following example, a record will be inserted and a new identifier will immediately be read:

Declare @intEqId int
Insert into Eq(Make, Model, EqTypeId)
Values ('ACME', 'Turbo', 2)
Select @intEqId = @@identity

If one Transact-SQL statement inserts several records into a table with an Identity column, @@identity will be set to the value from the last record:

Declare @intEqId int
Insert into Equipment(Make, Model, EqTypeId)
   Select Make, Model, EqTypeID
   From NewEquipment
Select @intEqId = @@identity

You will use this function very often. One of the most common types of stored procedures that you will write will just insert a record and return its new key to the caller.

After each Transact-SQL statement, the server sets the value of this variable to an integer value:

  • 0 If the statement was successful

  • Error number If the statement has failed

    This global variable is the foundation of all methods for error handling in the Microsoft SQL Server environment. It is essential to examine the value of this variable before any other Transact-SQL statement is completed, because the value of @@error will be reset. Even if the next statement is only a simple Select statement, the value of the @@error variable will be changed after it. In the following example, let's assume that an error will occur during the Update statement. @@error will contain the error code only until the next statement is executed; even the command for reading the @@error value will reset it. If it was completed successfully, SQL Server will set @@error to 0. The only way to preserve the @@error value is to immediately read it and store it in a local variable; then it can be used for error handling.

    Update Equipment
    Set EqTypeId = 3
    Where EqTypeId = 2
    Select @intErrorCode = @@error

    If it is necessary to read more than one global variable immediately after a statement, all such variables should be included in a single Select statement:

    Declare   @intEqId int,
              @intErrorCode int
    Insert into Equipment(Make, Model, EqTypeId)
    Values ('ACME', 'Turbo', 2)
    Select    @intEqId = @@identity,
              @intErrorCode = @@Error

    The @@error variable will be set to an error number only in the case of errors, not in the case of warnings. Supplementary information that the server posts regarding errors or warnings (that is, severity, state, and error messages) are not available inside a stored procedure or a batch. Only the error number is accessible from a stored procedure or a batch. Further components of error messages can be read only from the client application.

    You will find more details about use of the @@error function in the "Error Handling based on @@Error" section in Chapter 6.

    After each Transact-SQL statement, the server sets the value of this variable to the total number of records affected by it. It can be used to verify the success of selected operations:

    select Make, Model, EqTypeid
    into OldEquipment
    from Equipment
    where EqTypeid = 2
    if @@rowcount = 0
       Print "No rows were copied!"

    Certain statements (like the If statement) will set @@rowcount to 0, and certain statements (like Declare) will not affect it.

    Rowcount_big() is a function introduced in SQL Server 2000. It returns the number of affected records in the form of a bigint number.

    When you try to update an individual record, SQL Server will not report an error if your Where clause specifies a criterion that does not qualify any records. SQL Server will not update anything, and you might, for example, think that the operation was successful. You can use @@rowcount to identify such cases.

    Basic Transact-SQL Programming Constructs

     Home: Introduction
     Tip 1: T-SQL identifiers
     Tip 2: Database object qualifiers
     Tip 3: Character string data types
     Tip 4: Date, time and number data types
     Tip 5: special data types-Part 1
     Tip 6: Special data types-Part 2
     Tip 7: Local variables
     Tip 8: Global Variables
     Tip 9: Table variables
     Tip 10: Flow control statements
     Tip 11: Blocks, looping and conditional statements
     Tip 12: Unconditional and scheduled execution
     Tip 13: Transact SQL cursors
     Tip 14: Cursor related statements
     Tip 15: Problems and justified use of cursors

    The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.

    This was first published in February 2007

  • There are Comments. Add yours.

    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to: