Global variables

Your SQL Server maintains values in global variables. They carry information specific to the server or user session and can be examined from anywhere and return one value. Get more on this and how global variables begin with the @@ prefix and do not need to be declared.

This Content Component encountered an error

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.

@@identity 
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.

@@error
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.

@@rowcount 
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!"

NOTE
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.

TIP
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


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

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close