Variables

Variables in Transact-SQL are the equivalent of variables in other programming languages, but due to the nature of the Transact-SQL language, their use and behavior are somewhat different.

SQL Server 2005 (and 2000) documentation recognizes only local variables and table variables. Documentation in SQL Server 7 and earlier versions was also referring to global variables. In SQL Server 2005 (and 2000) global variables are considered to be functions.

Local Variables

The scope of local variables is a batch (a set of T-SQL statements that is sent to SQL Server and executed simultaneously). This restriction implicitly includes a single stored procedure (because stored procedures are defined in a batch). This is a significant limitation. However, several workarounds can be used as solutions to this problem.

A stored procedure cannot access variables defined in other stored procedures. One way to pass values to and from stored procedures is to use parameters. Keep in mind that you are passing only the values associated with the variables, not references, as you can in some other programming languages.

Another way to transfer value between stored procedures or between batches is the use of more permanent database objects such as tables or temporary tables.

Let's review basic operations with local variables.

Declaring Variables

Before you can do anything with a local variable, you need to declare it. Declaration consists of the reserved word Declare and a list of variables and their respective data types.

The names of variables must comply with the rules for identifiers with one exception—they must begin with @:

      Declare @LastName varchar(50) 

It is possible to define several variables in a single Declare statement. You just need to separate them with commas:

 Declare     @LastName varchar(50),             @FirstName varchar(BO),             @BirthDate smalldatetime 

Note 

One stored procedure or a batch can have up to 10,000 local variables.

You can define variables based on user-defined data types:

 Declare @OfficePhone phone 

Note 

You cannot define the nullability of the variable, as you can with table columns. This does not mean that variables cannot contain null values. In fact, before assignment, the value of each variable is null. It is also possible to explicitly set the value of each variable to null.

Assigning Values with the Select Statement

There are several ways to assign a value to a local variable. In early versions of SQL Server, the only way to do this was to use a modification of the Select statement:

      Select @LastName = 'Smith' 

It is also possible to assign several variables in the same statement:

 Select     @LastName = 'Smith',            @FirstName = 'David',            @BirthDate = '2/21/1965' 

Note 

It is necessary to assign a value of an appropriate data type to the variable; however, there are some workarounds. In some cases, the server will perform an implicit conversion from one data type to another. SQL Server also includes a set of functions for explicit conversion. Convert!) and Cast() can he used to change the data type of the value (see Chapter 4). Some data types are not compatible, so explicit conversion is the only solution.

Quite often, variables are assigned values from the result set of the Select statement:

      Select  $copy;Make = Equipment.Make,      $copy;Model = Equipment.Model, $copy;EqType = Equipment.EqType      From EqType INNER JOIN Equipment      ON EqType.EqTypeId = Equipment.EqTypeId Where EqId = 2 

There are some potential problems associated with this approach. How will the server assign values if the result set contains multiple records, or no records?

If more than one record is returned in the result set, a variable will be assigned the values from the last record. The only trouble is that we cannot predict which record will be the last, because this position depends on the index that the server uses to create the result set.

It is possible to create workarounds to exploit these facts (that is, to use hints to specify an index or use minimum and/or maximum functions to assign extreme values). The recommended solution, however, is to narrow the search criteria so that only one record is returned.

The other behavior that might cause unexpected results is the case in which a result set does not return any records. It is a common belief and expectation of many developers that the variable will be set to null. This is absolutely incorrect. The content of the variable will not be changed in this case.

Observe the following example, or try to run it against the Asset5 database:

 Declare     @make varchar(50),             @model varchar(50),             @EqType varchar(50) Select     @Make = 'ACME',            @Model = 'Turbo',            @EqType = 'cabadaster' Select     @Make = make,            @Model = Model,            @EqType = EqType.EqType From EqType INNER JOIN Equipment      ON EqType.EqTypeId = Equipment.EqTypeId Where EqId = -1 Select @make make, @model model, @EqType EqType 

Since the Equipment table does not have a record with the identifier set to −1, the variables will keep their original values.

 make                        model                        EqType ------------------------------------------------------------------------ ACME                        Turbo                        cabadaster (1 row(s) affected) 

Only if the values of the variables were not previously set will they continue to contain a null value.

The variable can be assigned with any Transact-SQL expression such as a constant or a calculation, or even a complete Select statement that returns a single value:

 Select     @Make = Make,            @Model = Model,            @EquipmentName = Make + ' ' + Model,            @EqType = (select EqType                      from dbo.EqType EqType                      where EqType.EqTypeId = Equipment.EqTypeId) From dbo.Equipment Where EqId = 2 

There is one combination of statements and expressions that will result in a syntax error. It is not possible to return a result set from the Select statement and to assign a variable in the same Select statement:

 Select     Make,                   @Model = Model     -- wrong From dbo.Equipment Where EqId = 2 

Assigning Values with the Set Statement

In SQL Server 7.0, the syntax of the Set statement has been expanded to support the assignment of local variables. In earlier versions, it was possible to use the Set statement only to declare cursor variables. Today, Microsoft is proclaiming this as a preferred method for assigning variables:

 Set @LastName = 'Johnson' 

Use of the Set statement is preferable, since it makes code more readable and reduces the opportunity to make a mistake (assign a variable and return a result set at the same time).

There is just one problem with the Set statement—it is not possible to assign several values with one statement. You will be forced to write code like this:

      Set  @Make = 'ACME'      Set  @Model = 'Turbo'      Set  @EqType = 'cabadaster' 

Assigning Values in the Update Statement

The ability to set the values of local variables in an Update statement is a feature that is buried deep in the oceans of SQL Server Books OnLine. It is an element that was designed to solve concurrency issues when code needs to read and update a column concurrently:

      Update Inventory      Set $copy;mnsCost = Cost = Cost * @fltTaxRate      Where InventoryId = @intInventoryId 

Displaying the Values of Variables

The value of a variable can be displayed to the user by using a Select or a Print statement:

      Select @LastName      Print @FirstName 

It is possible to include a local variable in a result set that will be returned to the user:

      Select    make "Selected make",      Model "Selected Model",      @Model "Original model" From Equipment Where EqId = 2 

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 columns 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 = @@sError 

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.

Table Variables

Table variables are objects similar to temporary tables and were introduced in SQL Server 2000. A table variable is declared using the table data type. A statement declaring a table variable initializes the variable as an empty table with a specified structure. As a table definition, such a statement includes definitions of columns with their data type, size, precision, and optional constraints (primary key, identity, unique, and check constraints). All elements have to be defined during the declaration. It is not possible to alter or add them later.

The following batch declares a table variable, inserts rows, and returns them to the user:

 Declare @MyTableVar table      (Id int primary key,      Lookup varchar(15)) Insert @MyTableVar values (1, '1Q2000'} Insert @MyTableVar values (2, '2Q2000'} Insert @MyTableVar values (3, '3Q2000'} Select * from @MyTableVar Go 

Because of their nature, table variables have certain limitations:

• Table variables can only be part of the Select, Update, Delete, Insert, and Declare Cursor statements.

• Table variables can be used as a part of the Select statement everywhere tables are acceptable, except as the destination in a Select…Into statement:

        Select Lookupld, Lookup      Into ©TableVariable     -- wrong      From Lookup 

• Table variables can be used in Insert statements except when the Insert statement collects values from a stored procedure:

        Insert into @TableVariable     -- wrong           Exec prMyProcedure 

• Unlike temporary tables, table variables always have a local scope. They can be used only in the batch, stored procedure, or function in which they are declared.

• Table variables are considered to be nonpersistent objects, and therefore they will not be rolled back after a Rollback Transaction statement.

Tip 

If possible, use table variables instead of temporary tables. Table variables bave less locking overbead and tberefore are faster.