Working with Variables in T-SQL, Part 1

T-SQL supports variables, as do most other development environments. However, T-SQL restricts the use of variables in ways that aren't common in other development environments. Let's begin by covering the basics of T-SQL variables, then look at the less common aspects of working with variables in T-SQL.

Declaring Variables

To declare a variable in T-SQL, you use the DECLARE statement:

DECLARE <@var_nam> 

For example, you would declare the variable @i as an integer by using the statement

DECLARE @i int

Although Microsoft doesn't document this feature, T-SQL also supports specifying the AS keyword between the variable's name and its data type, as in the following statement:

DECLARE @i AS int

I prefer to use the AS keyword because I find that it makes the DECLARE statement easier to read. The only data type that doesn't let you specify the AS keyword is the table data type, which is new in SQL Server 2000. The table data type lets you define a variable that holds a complete table. The following example shows an invalid use of the AS keyword with a table variable:

DECLARE @mytable AS table(  col1 int NOT NULL)

You must remove the AS keyword to make this code valid:

DECLARE @mytable table(  col1 int NOT NULL)

T-SQL supports only local variables. A local variable is available only in the batch that created it. A batch is a T-SQL statement (or group of statements) that SQL Server parses as a unit. Each client tool or interface has its own way of specifying where a batch ends. For example, in Query Analyzer, you use the GO command to specify where the batch ends. If you have a syntax error in any statement in the batch, the batch doesn't pass the parsing phase and the client tool doesn't send the batch to SQL Server for further processing. You can run the following code—which declares a table variable, then inserts a row into the table in the same batch—and notice that your code runs successfully:

DECLARE @mytable table(  col1 int NOT NULL)INSERT INTO @mytable VALUES(1)GO

Now, declare a table variable in one batch, then insert a row into the table in another batch:

DECLARE @mytable table(  col1 int NOT NULL)GOINSERT INTO @mytable VALUES(1)GO

The INSERT statement fails because the table variable is out of scope, and you get the following error message:

Server: Msg 137, Level 15, State 2, Line 2Must declare the variable '@mytable'.

Assigning Values to Variables

You can assign values to variables in several different ways, each of which has its own rules. If you're not familiar with all the rules, you might get unexpected results. Before SQL Server 7.0, the only way to assign values to variables was to use an assignment SELECT statement. In SQL Server 7.0, Microsoft added the SET command, which lets you assign values to variables. The SET command is ANSI-compliant and is the recommended way to assign values to variables. The SET command's syntax is

SET <@var_name> = 

where can be any expression that results in one value. For example, the following code assigns the value 100 to the variable @i:

DECLARE @i AS intSET @i = 100

However, you can't use a SET statement to assign values to more than one variable. To make several assignments, you must use several SET statements, such as

DECLARE @i AS int, @j AS intSET @i = 100SET @j = 200

The scalar expression can also be a scalar subquery—a query enclosed in parentheses that returns one value.

For example, run the following code against the Northwind database's Orders table to assign to the @custid variable the CustomerID of the customer who placed order 10248:

USE NorthwindDECLARE @custid AS nvarchar(5)SET @custid = (SELECT CustomerID  FROM Orders  WHERE OrderID = 10248)SELECT @custid

This code assigns the CustomerID to @custid and outputs the CustomerID, which the subquery retrieved (in this case, the CustomerID is VINET). If the scalar subquery didn't return a value, the code would set the variable to NULL.

For example, if you ran the above code with an OrderID that wasn't in the Orders table, as in the following example, the code would set @custid to NULL:

USE NorthwindDECLARE @custid AS nvarchar(5)SET @custid = N'XXXXX'SET @custid = (SELECT CustomerID  FROM Orders  WHERE OrderID = 99999)SELECT @custid

I assigned the value N'XXXXX' to the variable @custid before the second assignment to make two things clear: that the code sets the variable to NULL, and that the variable doesn't retain the N'XXXXX' value after the second assignment. However, if the subquery tries to return more than one value and follows an =, !=, <, <=, >, or >= operator or if you use the subquery as an expression, the subquery generates an error and SQL Server won't set the variable (i.e., the variable will retain the value that it had before the second assignment).

Suppose you want to return the OrderID of the order that customer ALFKI placed:

DECLARE @orderid AS intSET @orderid = -1SET @orderid = (SELECT OrderID  FROM Orders  WHERE CustomerID = N'ALFKI')SELECT @orderid

The output from this code is the message Server: Msg 512, Level 16, State 1, Line 3. In this example, the code first assigns a value of -1 to the variable @orderid. But the second assignment fails and generates an error message because the subquery tries to return more than one value. Therefore, the variable keeps its previous value of -1.

You can also use a SELECT statement to assign values to variables by using a syntax called assignment SELECT. You can use an assignment SELECT in two ways. One way uses the same syntax and follows the same rules as the SET statement, except that you can assign multiple variables in the same statement. You can use this syntax in all the previous code examples by simply replacing the SET keyword with the SELECT keyword; the results will be the same. However, the following example shows how you can use an assignment SELECT to set values for more than one variable in the same statement:

DECLARE @i AS int, @j AS intSELECT @i = 100, @j = 200

You can also use an assignment SELECT to assign the values returned from any regular SELECT statement to one or more variables. For example, the following code assigns the CustomerID of the customer who placed order 10248 to the variable @custid:

DECLARE @custid AS nvarchar(5)SELECT @custid = CustomerIDFROM OrdersWHERE OrderID = 10248SELECT @custid

The output from this code is VINET. Because this code uses an assignment SELECT, the query doesn't return a rowset; the code only assigns a value to a variable.

The difference between using an assignment SELECT and using a subquery to the right of the equal (=) sign might not be apparent from this example because both approaches assign the same value to the variable. However, an assignment SELECT assigns a value to the variable for each row that meets the query's filter criteria. In the previous example, only one row qualifies. In the query that Listing 1 shows, no row qualifies, and the output is XXXXX. Because no row meets the query's criteria, the query doesn't set the variable, which keeps the value that it had before the assignment SELECT. When I used a subquery to assign the CustomerID of a nonexisting order to a variable, the code set the variable to NULL.

Don't confuse a query that returns no rows with a query that aggregates an empty set. If your query is an aggregate query, it always returns rows, even if it receives an empty set as input. Therefore, because the query in Listing 2 aggregates an empty set, the assignment that Listing 2 shows sets @custid to NULL. If more than one row meets the query's filter criteria, the query sets the variable once for each row. However, the assignment doesn't fail as it did when I used a subquery that returned more than one value. After the assignment SELECT finishes, the variable has the value of the most recent assignment performed when the code accessed the last row. For example, Listing 3 shows a revision of the earlier query, which returned more than one value and failed with error 512. The output from Listing 3 is 11011.

Although customer ALFKI has more than one order, the assignment succeeds because order 11011 is the last order that the code accessed. When you use the syntax that Listing 3 shows, the code performs multiple assignments, each overriding the variable's previous value. So, you can use this technique to concatenate values. For example, you can use the assignment SELECT that Listing 4 shows to produce a string of OrderIDs for all the orders that customer ALFKI placed. The output from Listing 4 is 10643;10692;10702;10835;10952;11011;.

In SQL Server 2000, you can also generalize the assignment SELECT that Listing 4 shows. For example, you can create a scalar user-defined function (UDF) that accepts a CustomerID as an argument and returns a string containing that customer's OrderIDs in a concatenated list. Listing 5 shows such a UDF. To get the list of orders for customer ALFKI, execute the following statement:

SELECT dbo.fn_cust_orders(N'ALFKI')

This statement returns 10643;10692;10702;10835;10952;11011;. To get a list of orders for each customer in the Northwind database, execute the following statement:

SELECT  CustomerID,  dbo.fn_cust_orders(CustomerID) AS OrdersFROM Customers

Table 1 shows part of the output from this query.

No Such Thing as Global Variables

T-SQL doesn't support global variables (variables that are available to multiple batches and multiple sessions); it supports only variables that are local to the current batch in the current session that the user opened against SQL Server. As I mentioned earlier, if you define a local variable outside a certain routine (e.g., routine A), that routine's code can't reference the variable because you declared the variable and the routine in different scopes. Some programmers and the documentation for earlier releases of SQL Server incorrectly refer to niladic (i.e., parameterless) system functions—such as @@identity or @@error, which start with double at (@) signs—as global variables. Niladic system functions aren't global variables; you can't declare them or explicitly set them equal to a value. Niladic system functions are just system functions without parameters.

The confusion surrounding niladic system functions is probably why Microsoft added some new SQL Server 2000 functions that complement existing @@ functions but whose names don't begin with the @@ prefix. For example, the function @@rowcount returns as an integer value the number of rows that the last statement affected. However, SQL Server 2000 introduced the function ROWCOUNT_BIG(), which also returns the number of rows that the last statement affected—but as a big integer, which is an 8-byte integer instead of a regular 4-byte integer. The function @@identity returns the most recent IDENTITY value that the current session inserted into all scopes. But the new function SCOPE_IDENTITY() returns the most recent IDENTITY value that the current session inserted into the current scope.

Some programmers also confuse local variables whose names start with @@ with global variables. You might even find places in SQL Server Books Online (BOL) where local variables have @@ names. But whether a local variable's name starts with @ or @@, it's still a local variable. An interesting tidbit: You can even create a local variable that has a name of either @@ or @ with no other characters. The following statement declares the variable @@:

DECLARE @@ AS intSET @@ = 1SELECT @@

The output for this code is 1. The next statement declares the variable @:

DECLARE @ AS intSET @ = 1SELECT @

The output for this code is also 1. However, using such variable names can only lead to trouble in maintaining the code.

Alternatives to Global Variables

Although T-SQL doesn't support global variables, you can use temporary tables to accomplish the same functions. (T-SQL also doesn't support arrays. To see how to work around this limitation, see the sidebar "Alternatives to Arrays.") For example, if you want global variables that are accessible to all connections in all scopes, you can create a global temporary table (i.e., a temporary table that's available to multiple batches and multiple sessions) with only one row, which will contain your pseudo global variables. Suppose you need two integer pseudo global variables, named g1 and g2, and a varchar(10) pseudo global variable, named g3. You can create a temporary table called ##Globals that declares these variables:

CREATE TABLE ##Globals(  g1 int NULL,  g2 int NULL,  g3 varchar(10) NULL)

To initialize all the variables in the temporary table to NULL, insert one row with DEFAULT VALUES:

INSERT INTO ##Globals DEFAULT VALUES

The DEFAULT VALUES clause causes the INSERT statement to use the column's default value or NULL if the column has no default. To assign a value to a pseudo global variable, use an UPDATE statement. For example, to set the value of g2 to 10, execute the following statement:

UPDATE ##Globals SET g2 = 10

To retrieve the value of a pseudo global variable, use a SET statement with a subquery:

DECLARE @g2 AS intSET @g2 = (SELECT g2 FROM ##Globals)SELECT @g2

The output from this statement is 10. To add a pseudo global variable and initialize it to NULL, add to the table a column that allows NULLs. For example, to add a decimal(8, 2) pseudo global variable, named g4, and initialize it to NULL, execute the following statement:

ALTER TABLE ##Globals ADD g4 decimal(8, 2) NULL

To drop a pseudo global variable, issue a DROP COLUMN command on the appropriate column in the table. For example, to drop the g4 pseudo global variable, execute

ALTER TABLE ##Globals DROP COLUMN g4

Note that you can't drop all the columns from the ##Globals table; you must leave at least one column. If this limitation is a problem, you can add to the table a dummy column that you leave in the empty table.

Although the temporary table alternative to global variables works, it has several problems. First, some process needs to take responsibility for creating the ##Globals temporary table. SQL Server maintains a reference counter for a global temporary table that counts the number of different users who accessed the table. SQL Server usually drops the table when the connection that created the table disconnects and the reference counter becomes 0, meaning that no user is using the table. Therefore, to keep the ##Globals temporary table in existence, the process that created the table must remain active while SQL Server is active.

If you create the ##Globals temporary table inside a stored procedure and mark it as a startup procedure, SQL Server invokes that stored procedure whenever SQL Server starts. In addition, if a stored procedure creates a global temporary table at startup, SQL Server ensures that the table's reference counter never goes to 0. Thus, SQL Server guarantees that this global temporary table remains in the system as long as SQL Server is active—unless someone explicitly drops it. To create a stored procedure that creates the ##Globals temporary table and to mark that procedure as a startup procedure, execute the code that Listing 6 shows.

The second problem you might face if you use the temporary table alternative is that you might run out of global variables. The maximum number of columns that a SQL Server 2000 or 7.0 table allows is 1024. If you're working with SQL Server 2000 and you need more than 1024 global variables, you can use a different schema for the ##Globals table, which will hold each global variable in a different row as opposed to a different column. You can use one column to store the global variable's name and another sql_variant column to store the value, as the following code shows:

-- Make sure you drop the old version of-- ##Globals first.DROP TABLE ##GlobalsGOCREATE TABLE ##Globals(  var_name sysname NOT NULL PRIMARY KEY,  value sql_variant NULL)

Run the following code to add four global variables to the ##Globals table and initialize them to NULL:

INSERT INTO ##Globals VALUES(N'g1', CAST(NULL AS int))INSERT INTO ##Globals VALUES(N'g2', CAST(NULL AS int))INSERT INTO ##Globals VALUES(N'g3', CAST(NULL AS varchar(10)))INSERT INTO ##Globals VALUES(N'g4', CAST(NULL AS decimal(8, 2)))

To set, remove, or retrieve a variable, use an UPDATE, DELETE, or SELECT statement (respectively) and add a WHERE clause to the query that filters the appropriate variable:

WHERE var_name = 

Third, you might also face concurrency problems with a solution that has only one row, which contains a column for each global variable. If a process modifies a pseudo global variable, the process exclusively locks the whole row until the transaction ends; in other words, no statement can access any global variable during that time. The revised SQL Server 2000 schema also disarms this problem. Each pseudo global variable is in a different row. If a process modifies a pseudo global variable, SQL Server locks only the row that contains the variable until the transaction ends.

Note that a global temporary table is accessible to all connected SQL Server users. Any user can retrieve and modify data in the table, and any user can drop the table. If you want to protect the temporary table from being dropped, for example, you can have the startup procedure create a permanent table in tempdb instead of a temporary table. You can then have the startup procedure issue the appropriate GRANT and DENY permissions on the table for the public role.

Each time SQL Server starts, it recreates tempdb as a copy of the model database. The startup procedure creates the Globals table in tempdb and issues GRANT and DENY permissions to establish the appropriate permissions on the table. If you implement the Globals table as a permanent table in tempdb, however, you must use a three-part object name to access it: tempdb..Globals (owner omitted).

To allow access to the pseudo global variables only from your connection, you must manually create the table as a local temporary table by naming it #Globals. The table will be available to all scopes of your connection (e.g., to a stored procedure that you invoke), and SQL Server will drop the table automatically when you disconnect.

What's Next?

This article is Part 1 for a reason. I have a lot more to cover about T-SQL variables. In Part 2, I'll discuss how to use variables in UPDATE statements and how to assign values to variables dynamically. Until then, don't take any wooden global variables—or arrays.