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), 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.