Problem

You want to declare a variable and use it in subsequent T-SQL statements. For example, you want to build a search string, store that search string into a variable, and reference the string in the WHERE clause of a subsequent query.

Solution

Execute a DECLARE statement. Specify the variable and the data type. Optionally provide an initial value.

The following example demonstrates using a variable to hold a search string. The variable is declared and initialized to a value. Then a SELECT statement finds people with names that include the given string.

DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz';
SELECT AddressID, AddressLine1
FROM Person.Address
WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%';

The query in this example returns all rows with an address containing the search string value.

AddressID	AddressLine1
-----------     ---------------
20333           Heiderplatz 268
17062           Heiderplatz 268
24962           Heiderplatz 662
15742           Heiderplatz 662
27109           Heiderplatz 772
23496           Heiderplatz 772
…

How It Works

Throughout the book you'll see examples of variables being used within queries and module-based SQL Server objects (stored procedures, triggers, and more). Variables are objects you can create to temporarily contain data. Variables can be defined across several different data types and then referenced within the allowable context of that type.

The solution query begins by declaring a new variable that is prefixed by the @ symbol and followed by the defining data type that will be used to contain the search string. Here's an example:

DECLARE @AddressLine1 nvarchar(60)

Next and last in the declaration is the initial value of the variable:

DECLARE @AddressLine1 nvarchar(60) = 'Heiderplatz';

You can also specify a value by executing a SET statement, and prior to SQL Server 2008, you are required to do so. Here's an example:

DECLARE @AddressLine1 nvarchar(60);
SET @AddressLine1 = 'Heiderplatz';

Next the solution executes a query referencing the variable in the WHERE clause, embedding it between the % wildcards to find any row with an address containing the search string:

WHERE AddressLine1 LIKE '%' + @AddressLine1 + '%'

It's possible to declare a variable without assigning a value. In that case, the variable is said to be null. Here's an example:

DECLARE @AddressLine1 nvarchar(60);
SELECT @AddressLine1;
Results from this query are as follows:
---------------------------------------
NULL
(1 row(s) affected)

It is the same with a variable as with a table column. A null column is one having no value. Likewise, a null variable is one having no value.

Problem

You want to retrieve a value from the database into a variable for use in later T-SQL code.

Solution

Issue a query that returns zero or one rows. Specify the primary key, or a unique key, of the target row in your WHERE clause. Assign the column value to the variable, as shown in the following example:

DECLARE @AddressLine1 nvarchar(60);
DECLARE @AddressLine2 nvarchar(60);
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 66;
SELECT @AddressLine1 AS Address1, @AddressLine2 AS Address2;

The results are as follows:

Address1          Address2
--------------    ----------
4775 Kentucky     Dr. Unit E

How It Works

The solution query retrieves the two address lines for address #66. Because AddressID is the table's primary key, there can be only one row with ID #66. A query such as in the example that can return at most one row is sometimes termed a singleton select.

The key syntax aspect to focus on is the following pattern in the SELECT list for assigning values returned by the query to variables that you declare:

@VariableName = ColumnName

The solution query contains two such assignments: @AddressLine1 = AddressLine1 and @AddressLine2 = AddressLine2. They assign the values from the columns AddressLine1 and AddressLine2, respectively, into the variables @AddressLine1 and @AddressLine2.

What if your query returns no rows? In that case, your target variables will be left unchanged. For example, execute the following query block:

DECLARE @AddressLine1 nvarchar(60) = '101 E. Varnum'
DECLARE @AddressLine2 nvarchar(60) = 'Ambulance Desk'
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 49862;
SELECT @AddressLine1, @AddressLine2;

You will get the following results:

------------- ---------------
101 E. Varnum Ambulance Desk

Now you have a problem. How do you know whether the values in the variables are from the query or whether they are left over from prior code? One solution is to test the global variable @@ROWCOUNT. Here's an example:

DECLARE @AddressLine1 nvarchar(60) = '101 E. Varnum'
DECLARE @AddressLine2 nvarchar(60) = 'Ambulance Desk'
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 49862;
IF @@ROWCOUNT = 1
  SELECT @AddressLine1, @AddressLine2
ELSE
  SELECT 'Either no rows or too many rows found.';

If @@ROWCOUNT is 1, then our singleton select is successful. Any other value indicates a problem. A @@ROWCOUNT of zero indicates that no row was found. A @@ROWCOUNT greater than zero indicates that more than one row was found. If multiple rows are found, you will arbitrarily be given the values from the last row in the result set. That is rarely desirable behavior and is the reason for our strong admonition to query by either the primary key or a unique key.

Problem

You want to write an IF…THEN…ELSE statement so that you can control which of two possible code paths is taken.

Solution

Write your statement using the following syntax:

IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]

For example, the following code block demonstrates executing a query conditionally based on the value of a local variable:

DECLARE @QuerySelector int = 3;
IF @QuerySelector = 1
BEGIN
 SELECT TOP 3 ProductID, Name, Color
 FROM Production.Product
 WHERE Color = 'Silver'
 ORDER BY Name
END
 ELSE
BEGIN
 SELECT TOP 3 ProductID, Name, Color
 FROM Production.Product
 WHERE Color = 'Black'
 ORDER BY Name
END;

This code block returns the following results:

ProductID     Name                      Color
---------     ---------------------     ------
322           Chainring                 Black
863           Full-Finger Gloves, L     Black
862           Full-Finger Gloves, M     Black

How It Works

In this recipe, an integer local variable is created called @QuerySelector. That variable is set to the value of 3. Here is the declaration:

DECLARE @QuerySelector int = 3;

The IF statement begins by evaluating whether @QuerySelector is equal to 1:

IF @QuerySelector = 1

If @QuerySelector were indeed 1, the next block of code (starting with the BEGIN statement) would be executed:

BEGIN
 SELECT TOP 3 ProductID, Name, Color
 FROM Production.Product
 WHERE Color = 'Silver'
 ORDER BY Name
END

Because the @QuerySelector variable is not set to 1, the second block of T-SQL code is executed, which is the block after the ELSE clause:

BEGIN
 SELECT TOP 3 ProductID, Name, Color
 FROM Production.Product
 WHERE Color = 'Black'
 ORDER BY Name
END;

Your IF expression can be any expression evaluating to TRUE, FALSE, or NULL. You are free to use AND, OR, and NOT; parentheses for grouping; and all the common operators that you are used to using for equality, greater than, less than, and so forth. The following is a somewhat contrived example showing some of the possibilities:

IF (@QuerySelector = 1 OR @QuerySelector = 3) AND (NOT @QuerySelector IS NULL)

Execute the solution example using this version of the IF statement, and you'll get the silver color parts:

ProductID     Name               Color
---------     ---------          --------
952           Chain              Silver
320           Chainring Bolts    Silver
321           Chainring Nut      Silver

Because the solution example is written with only one statement in each block, you can omit the BEGIN…END syntax. Here's an example:

DECLARE @QuerySelector int = 3;
IF @QuerySelector = 1
  SELECT TOP 3 ProductID, Name, Color
  FROM Production.Product
  WHERE Color = 'Silver'
  ORDER BY Name
ELSE
  SELECT TOP 3 ProductID, Name, Color
  FROM Production.Product
  WHERE Color = 'Black'
  ORDER BY Name;

BEGIN is optional for single statements following IF, but for multiple statements that must be executed as a group, BEGIN and END must be used. As a best practice, it is easier to use BEGIN…END for single statements, too, so that you don't forget to do so if/when the code is changed at a later time.

Problem

You have a single expression, table column, or variable that can take on a well-defined set of possible values. You want to specify an output value for each possible input value. For example, you want to translate department names into conference room assignments.

Solution

Write a CASE expression associating each value with its own code path. Optionally, include an ELSE clause to provide a code path for any unexpected values.

For example, the following code block uses CASE to assign departments to specific conference rooms. Departments not specifically named are lumped together by the ELSE clause into Room D.

SELECT DepartmentID AS DeptID, Name, GroupName,
   CASE GroupName
   WHEN 'Research and Development' THEN 'Room A'
   WHEN 'Sales and Marketing' THEN 'Room B'
   WHEN 'Manufacturing' THEN 'Room C'
   ELSE 'Room D'
   END AS ConfRoom
FROM HumanResources.Department

Results from this query show the different conference room assignments as specified in the CASE expression.

How It Works

Use a CASE expression whenever you need to translate one set of defined values into another. In the case of the solution example, the expression translates group names into a set of conference room assignments. The effect is essentially a mapping of groups to rooms.

The general format of the CASE expression in the example is as follows:

CASE ColumnName
  WHEN OneValue THEN AnotherValue
  …
ELSE CatchAllValue
END AS ColumnAlias

The ELSE clause in the expression is optional. In the example, it's used to assign any unspecified groups to Room D.

The result from a CASE expression in a SELECT statement is a column of output. It's good practice to name that column by providing a column alias. The solution example specifies AS ConfRoom to give the name ConfRoom to the column of output holding the conference room assignments, which is the column generated by the CASE expression.

Problem

You want to evaluate a series of expressions. When an expression is true, you want to specify a corresponding return value.

Solution

Write a so-called searched CASE expression, which you can loosely think of as similar to multiple IF statements strung together. The following is a variation on the query from Recipe 2-4. This time, the department name is evaluated in addition to other values, such as the department identifier and the first letter of the department name.

SELECT DepartmentID, Name,
   CASE
   WHEN Name = 'Research and Development' THEN 'Room A'
   WHEN (Name = 'Sales and Marketing' OR DepartmentID = 10) THEN 'Room B'
   WHEN Name LIKE 'T%'THEN 'Room C'
   ELSE 'Room D' END AS ConferenceRoom
FROM HumanResources.Department;

Execute this query, and your results should look as follows:

DepartmentID     Name                         ConferenceRoom
------------     -------------------------    --------------
12               Document Control              Room D
1                Engineering                   Room D
16               Executive                     Room D
14               Facilities and Maintenance    Room D
10               Finance                       Room B
9                Human Resources               Room D
11               Information Services          Room D
4                Marketing                     Room D
7                Production                    Room D
8                Production Control            Room D
5                Purchasing                    Room D
13               Quality Assurance             Room D
6                Research and Development      Room A
3                Sales                         Room D
15               Shipping and Receiving        Room D
2                Tool Design                   Room C

How It Works

CASE offers an alternative syntax that doesn't use an initial input expression. Instead, one or more Boolean expressions are evaluated. (A Boolean expression is most typically a comparison expression returning either true or false.) The general form as used in the example is as follows:

CASE
 WHEN Boolean_expression_1 THEN result_expression_1
 …
 WHEN Boolean_expression_n THEN result_expression_n
 ELSE CatchAllValue
END AS ColumnAlias

Boolean expressions are evaluated in the order you list them until one is found that evaluates as true. The corresponding result is then returned. If none of the expressions evaluates as true, then the optional ELSE value is returned. The ability to evaluate Boolean expressions of arbitrary complexity in this flavor of CASE provides additional flexibility above the simple CASE expression from the previous recipe.

Problem

You want to write a WHILE statement to execute a block of code so long as a given condition is true.

Solution

Write a WHILE statement using the following example as a template. In the example, the system stored procedure sp_spaceused is used to return the table space usage for each table in the @AWTables table variable.

-- Declare variables
DECLARE @AWTables TABLE (SchemaTable varchar(100));
DECLARE @TableName varchar(100);
-- Insert table names into the table variable
INSERT @AWTables (SchemaTable)
  SELECT TABLE_SCHEMA + '.' + TABLE_NAME
  FROM INFORMATION_SCHEMA.tables
  WHERE TABLE_TYPE = 'BASE TABLE'
  ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME;
-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0
BEGIN
  SELECT TOP 1 @TableName = SchemaTable
  FROM @AWTables
  ORDER BY SchemaTable;
  EXEC sp_spaceused @TableName;
  DELETE @AWTables
  WHERE SchemaTable = @TableName;
END;

Execute this code, and you will get multiple result sets—one for each table—similar to the following:

How It Works

The example in this recipe demonstrates the WHILE statement, which allows you to repeat a specific operation or batch of operations while a condition remains true. The general form for WHILE is as follows:

WHILE Boolean_expression
BEGIN
  { sql_statement | statement_block }
END;

WHILE will keep the T-SQL statement or batch processing while the Boolean expression remains true. In the case of the example, the Boolean expression tests the result of a query against the value zero. The query returns the number of values in a table variable. Looping continues until all values have been processed and no values remain.

In the example, the table variable @AWTABLES is populated with all the table names in the database using the following INSERT statement:

INSERT @AWTables (SchemaTable)
  SELECT TABLE_SCHEMA + '.' + TABLE_NAME
  FROM INFORMATION_SCHEMA.tables
  WHERE TABLE_TYPE = 'BASE TABLE'
  ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME;

The WHILE loop is then started, looping as long as there are rows remaining in the @AWTables table variable:

WHILE (SELECT COUNT(*) FROM @AWTables) > 0

Within the WHILE, the @TableName local variable is populated with the TOP 1 table name from the @AWTables table variable:

SELECT TOP 1 @TableName = SchemaTable
FROM @AWTables
ORDER BY SchemaTable;

Then EXEC sp_spaceused is executed on against that table name:

EXEC sp_spaceused @TableName;

Lastly, the row for the reported table is deleted from the table variable:

DELETE @AWTables
WHERE SchemaTable = @TableName;

WHILE will continue to execute sp_spaceused until all rows are deleted from the @AWTables table variable.

Two special statements that you can execute from within a WHILE loop are BREAK and CONTINUE. Execute a BREAK statement to exit the loop. Execute the CONTINUE statement to skip the remainder of the current iteration. For example, the following is an example of BREAK in action to prevent an infinite loop:

WHILE (1=1)
BEGIN
  PRINT 'Endless While, because 1 always equals 1.';
  IF 1=1
   BEGIN
   PRINT 'But we won''t let the endless loop happen!';
   BREAK; --Because this BREAK statement terminates the loop.
   END;
END;

And next is an example of CONTINUE:

DECLARE @n int = 1;
WHILE @n = 1
BEGIN
  SET @n = @n + 1;
  IF @n > 1
   CONTINUE;
  PRINT 'You will never see this message.';
END;

This example will execute with one loop iteration, but no message is displayed. Why? It's because the first iteration moves the value of @n to greater than 1, triggering execution of the CONTINUE statement. CONTINUE causes the remainder of the BEGIN…END block to be skipped. The WHEN condition is reevaluated. Because @n is no longer 1, the loop terminates.

Problem

You want to discontinue execution of a stored procedure or T-SQL batch, possibly including a numeric return code.

Solution #1: Exit with No Return Value

Write an IF statement to specify the condition under which to discontinue execution. Execute a RETURN in the event the condition is true. For example, the second query in the following code block will not execute because there are no pink bike parts in the Product table:

IF NOT EXISTS
  (SELECT ProductID
   FROM Production.Product
   WHERE Color = 'Pink')
BEGIN
   RETURN;
END;
SELECT ProductID
FROM Production.Product
WHERE Color = 'Pink';

Solution #2: Exit and Provide a Value

You have the option to provide a status value to the invoking code. First, create a stored procedure along the following lines. Notice particularly the RETURN statements.

CREATE PROCEDURE ReportPink AS
IF NOT EXISTS
  (SELECT ProductID
   FROM Production.Product
   WHERE Color = 'Pink')
BEGIN
  --Return the value 100 to indicate no pink products
  RETURN 100;
END;
SELECT ProductID
FROM Production.Product
WHERE Color = 'Pink';
--Return the value 0 to indicate pink was found
RETURN 0;

With this procedure in place, execute the following:

DECLARE @ResultStatus int;
EXEC @ResultStatus = ReportPink;
PRINT @ResultStatus;

You will get the following result:

100

This is because no pink products exist in the example database.

How It Works

RETURN exits the current Transact-SQL batch, query, or stored procedure immediately. RETURN exits only the code executing in the current scope; if you have called stored procedure B from stored procedure A and if stored procedure B issues a RETURN, stored procedure B stops immediately, but stored procedure A continues as though B had completed successfully.

The solution examples show how RETURN can be invoked with or without a return code. Use whichever approach makes sense for your application. Passing a RETURN code does allow the invoking code to determine why you have returned control, but it is not always necessary to allow for that.

The solution examples also show how it sometimes makes sense to invoke RETURN from an IF statement and other times makes sense to invoke RETURN as a stand-alone statement. Again, use whichever approach best facilitates what you are working to accomplish.

Problem

You want to label a specific point in a T-SQL batch. Then you want the ability to have processing jump directly to that point in the code that you have identified by label.

Solution

Create a label using the following syntax, which is simply to provide a label name followed by a colon:

LabelName:

Then write a GOTO statement to branch directly to the point in the code that you have labeled. Here's an example:

GOTO LabelName;

The following is an example that checks whether a department name is already in use by an existing department. If so, the INSERT is bypassed using GOTO. If not, the INSERT is performed.

DECLARE @Name nvarchar(50) = 'Engineering';
DECLARE @GroupName nvarchar(50) = 'Research and Development';
DECLARE @Exists bit = 0;
IF EXISTS (
  SELECT Name
  FROM HumanResources.Department
  WHERE Name = @Name)
BEGIN
  SET @Exists = 1;
  GOTO SkipInsert;
END;
INSERT INTO HumanResources.Department
  (Name, GroupName)
  VALUES(@Name , @GroupName);
SkipInsert: IF @Exists = 1
BEGIN
  PRINT @Name + ' already exists in HumanResources.Department';
END
ELSE
BEGIN
 PRINT 'Row added';
END;

There is, in fact, a department named Engineering defined in the example database. So if you execute this code example, you should get the following result:

Engineering already exists in HumanResources.Department

How It Works

In this recipe's example, two local variables are declared and set to values in preparation for those values being inserted into the HumanResources.Department table:

DECLARE @Name nvarchar(50) = 'Engineering';
DECLARE @GroupName nvarchar(50) = 'Research and Development';

Another variable is defined to hold a bit value. This value acts as a flag to mark whether a row already exists in the table.

DECLARE @Exists bit = 0;

Next, an IF statement checks for the existence of any row with the same department name as the local variable. If such a row exists, the bit variable is set to 1 and the GOTO command is invoked. GOTO references the label name that you want to skip to, in this case called SkipInsert:

IF EXISTS (
  SELECT Name
  FROM HumanResources.Department
  WHERE Name = @Name)
BEGIN
  SET @Exists = 1;
  GOTO SkipInsert;
END;

The target label appears in the code as follows:

SkipInsert: IF @Exists = 1
…

It is also possible to, and perfectly reasonable to do so, write the label on a line by itself:

SkipInsert:
IF @Exists = 1
…

Following the label is another IF statement. If the bit flag is enabled, a PRINT statement gives a message stating that the row already exists:

SkipInsert: IF @Exists = 1
BEGIN
  PRINT @Name + ' already exists in HumanResources.Department';
END

Otherwise, a message is printed that the row was successfully added:

ELSE
BEGIN
 PRINT 'Row added';
END;

As a best practice, when given a choice between using GOTO and other control-of-flow methods, you should choose something other than GOTO. GOTO can decrease the clarity of the code, because you'll have to jump around the batch or stored procedure code in order to understand the original intention of the query author.

Problem

You want to pause execution for an amount of time or until a given time of day.

Solution

Execute the WAITFOR statement. With it you can delay a specific amount of time, or you can halt until a specific moment in time is reached.

The following is an example showing how to delay for a specific number of hours:minutes:seconds. The example delays for ten seconds and then executes the query following:

WAITFOR DELAY '00:00:10';
BEGIN
 SELECT TransactionID, Quantity
 FROM Production.TransactionHistory;
END;

Next is another example showing how to wait until a specific time is reached. The subsequent statement will execute at 12:22 p.m.

WAITFOR TIME '12:22:00';
BEGIN
 SELECT COUNT(*)
 FROM Production.TransactionHistory;
END;

How It Works

WAITFOR provides for two options: DELAY and TIME. Specify DELAY when you want to pause for a duration of time. Specify TIME when you want to pause until a given time of day is reached. For example, DELAY '12:22:00' pauses execution for 12 hours and 22 minutes. TIME '12:22:00' pauses execution until the next time it is 12:22 p.m.

Waiting for a certain amount of time is useful when you know another operation must execute asynchronously while your current batch process must wait. For example, if you have kicked off an asynchronous SQL Server Agent job using the sp_start_job system stored procedure, control is returned immediately to the batch after the job starts to execute. If you know that the job you just kicked off takes at least five minutes to run and your consecutive tasks are dependent on the completion of the job, WAITFOR can be used to delay processing until the job is complete.

Waiting until a specific time of day is handy when an operation must occur during a specific time period in the day. For example, say you have a stored procedure that performs data warehouse aggregations from transaction-processing tables. The aggregations may take a couple of hours to complete, but you don't want to load the finished data from the staging to the production tables until after business hours. Using WAITFOR TIME in the procedure, you can stop the final load of the tables until nonbusiness hours.

Problem

You need to implement row-by-row processing in your application. You don't want to fire off a single UPDATE or SELECT statement and let the database engine do the work. Instead, you want to “touch” each row and process it individually.

Solution

Implement cursor-based processing. A T-SQL cursor allows you to write row-by-row processing into your application, giving you full control over exactly what is one.

Although we recommend avoiding cursors whenever possible, using cursors for ad hoc, periodic database administration information gathering, as demonstrated in this next example, is usually perfectly justified.

The following code block demonstrates a cursor that loops through each session ID currently active on the SQL Server instance. The block executes SP_WHOon each session to see each session's logged-in user name and other data.

-- Do not show rowcounts in the results
SET NOCOUNT ON;
DECLARE @session_id smallint;
-- Declare the cursor
DECLARE session_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
  SELECT session_id
  FROM sys.dm_exec_requests
  WHERE status IN ('runnable', 'sleeping', 'running'),
-- Open the cursor
OPEN session_cursor;
-- Retrieve one row at a time from the cursor
FETCH NEXT
  FROM session_cursor
  INTO @session_id;
-- Process and retrieve new rows until no more are available
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'Spid #: ' + STR(@session_id);
  EXEC ('sp_who ' + @session_id);
  FETCH NEXT
   FROM session_cursor
   INTO @session_id;
END;
-- Close the cursor
CLOSE session_cursor;
-- Deallocate the cursor
DEALLOCATE session_cursor

Execute the code block. You'll get output as follows:

Spid #:        10
spid       ecid status     loginame     …
------     -----------     --------     …
10         0 sleeping      sa     …
…
Spid #:        52
spid       ecid status     loginame …
-----      -----------     ----------------------     …
10         0 runnable     Jonathan-T410Jonathan     …

This output indicates that I have no security access to view buffers connected to session 27. However, session 52 is my own session, and I am allowed to see my own buffer.

How It Works

Query authors with programming backgrounds are often more comfortable using Transact-SQL cursors than the set-based alternatives for retrieving or updating rows. For example, a programmer may decide to loop through one row at a time, updating rows in a singleton fashion, instead of updating an entire set of rows in a single operation. Often it's better to find a set-based solution, but there are some cases, as in the example, in which using a cursor is justifiable.

The code example illustrates the general life cycle of a T-SQL cursor, which is as follows:

1. A cursor variable is declared and associated with a SQL statement.

   DECLARE session_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
     SELECT session_id
     FROM sys.dm_exec_requests
   WHERE status IN ('runnable', 'sleeping', 'running'),

2. The cursor is then opened for use.

   OPEN session_cursor;

3. Rows can then be fetched one at a time.

   FETCH NEXT
     FROM session_cursor
     INTO @session_id;

4. Typically a WHILE loop is used to process and fetch as long as rows remain.

   WHILE @@FETCH_STATUS = 0
   BEGIN
     … Processing goes here …
     FETCH NEXT
      FROM session_cursor
      INTO @session_id;
   END;

5. The cursor is then closed.

   CLOSE session_cursor;

6. And finally, you should deallocate the cursor and associated memory.

   DEALLOCATE session_cursor;

The @@FETCH_STATUS function used in the example returns a code indicating the results from the preceding FETCH. Possible result codes are as follows:

• 0: The fetch operation was successful. You now have a row to process.
• -1: You have fetched beyond the end of the cursor or otherwise have attempted to fetch a row not included in the cursor's result set.
• -2: You have fetched what should be a valid row, but the row has been deleted since you have first opened the cursor, or the row has been modified such that it is no longer part of the cursor's query results.

Most often when doing row-by-row processing, you'll just process and fetch until the status is no longer zero. That's the precise approach taken in the solution example. The other codes come into play when you are executing variations on FETCH that allow you to specify specific result set rows by their absolute or relative positions in the set.

The difference between closing and deallocating a cursor is that closing a cursor retains the definition. You are able to reopen the cursor. Once you deallocate a cursor, the definition and resources are gone, as if you had never declared it in the first place.