Hadi Fadlallah
Fetch rows from SQL cursor without storing result into variables

An overview of the SQL cursor @@FETCH_STATUS function

January 23, 2020 by

SQL cursor is one of the most popular database objects. It is used to retrieve data from the result set of an SQL query one row at a time. Even if the cursor is not recommended from a performance perspective, they are still widely used especially when handling a small amount of data.

There are many related objects needed to use a cursor such as @@FETCH_STATUS function. In this article, we will try to describe SQL cursor briefly and then we will explain @@FETCH_STATUS function by providing some examples to clarify each of its returned values.

SQL cursor overview

As mentioned before, a cursor is a database object used to fetch the rows from a result set. There are different phases in a cursor life cycle:

Declaring cursor

First of all, we need to create a cursor object. Mainly there are 2 required parameters:

  1. Cursor name
  2. Source SQL query

You must use the following syntax to declare a cursor object:

Note that source SQL query should be a SELECT statement, as for example, the following cursor is to read the table schemas and names created in a database:

Opening cursor

Declaring SQL cursor doesn’t mean that you can start reading data from the result set, first, you need to retrieve the source SQL query result which is done using the OPEN command. The command syntax is very simple, just write OPEN keyword plus the name of the cursor as follows:

Fetching rows

After opening the cursor, the source SQL query is executed and the result set is stored in memory. The next step is to fetch rows and to visualize them or to store them within some declared variables. Note that each column in the result set must be mapped to a variable having a relevant data type.

The FETCH command syntax is as follows:

If you decide to only fetch rows without storing them within variables, the result will be displayed as a result of a select query:

Fetch rows from SQL cursor without storing result into variables

Otherwise, if you choose to store the fetched rows within SQL variable you have to make sure that the order of the variables must be the same as the result set columns order:

Fetching rows from SQL cursor into variables

Closing and deallocating cursor

After consuming rows, you should close it to release the current result set and to free any cursor locks held on the rows on which the cursor is positioned. The command syntax is very simple, just write CLOSE keyword plus the name of the cursor as follows:

After closing the cursor, you should make sure that the cursor object reference is released from the memory by using the DEALLOCATE command as following:

@@FETCH_STATUS function

After illustrating the main commands used to manipulate SQL cursors, there are some system functions that support cursors such as @@FETCH_STATUS and @@CURSOR_ROWS. In this section, we will explain the @@FETCH_STATUS function by providing some examples.

What is the @@FETCH_STAUTS function?

@@FETCH_STATUS is a system function that returns the status of the last FETCH statement issued against any opened cursor. This function returns an integer value as mentioned in the table below (Reference: @@FETCH_STATUS (Transact-SQL)):

Value

Description

0

The FETCH statement was successful

-1

The FETCH statement failed, or the row was beyond the result set

-2

The row fetched is missing

-9

The cursor is not performing a fetch operation

One of the main use cases for this function is to implement it within a while loop to keep fetching rows while the fetch statement is successful instead of writing several FETCH statements for each cursor. As an example:

In the example above, we have created a SQL cursor to fetch the result of a SQL command that selects the name and the schema of tables created within the database. After declaring and opening the cursor, we issued the first FETCH statement. If the SQL query returned at least one row the first FETCH statement should be successful, else it should fail. After that, we used a WHILE loop to check if the FETCH statement was successful and to keep fetching rows while there are more rows to be fetched. When the FETCH statement doesn’t return any rows @@FETCH_STATUS function should return -1, and then the while loop is ended.

As mentioned in the previous section, it is very simple to give an example of how the @@FETCH_STATUS returns 0 and -1 values. But what about -2 and -9? In the next section, we will briefly explain these values by providing some examples.

The row fetched is missing (@@FETCH_STATUS = -2)

This FETCH status is returned if a row that should be returned in the FETCH statement is deleted in case that we used the KEYSET option while declaring the cursor (note that KEYSET option specifies that the membership and order of the rows in the cursor are fixed when the cursor is opened). This scenario happens when different users are working with the same data and one user deletes some rows while the other is trying to manipulate them using SQL cursor.

The following example is only to illustrate this scenario:

As shown in the following screenshot you can see that the @@FETCH_STATUS function returned a value of -2:

@@fetch_status = -2 example

For more information about KEYSET and other options that can be used while declaring SQL cursors you can refer to the following documentation: DECLARE CURSOR (Transact-SQL)

The cursor is not performing a fetch operation (@@FETCH_STATUS = -9)

This value is not returned by the @@FETCH_STATUS function. It is a value stored within SQL Server internals and it is shown in the System Dynamic Management Views when the SELECT statement is defined. Declaring the SQL cursor will never fetch data (no information about the result set columns) regardless the cursor is open or not. The simplest example is to declare a cursor with a SELECT query that has no columns and only select NULL:

As shown in the following screenshot, the result will show -9:

@@fetch_status = -9 example

Note that, if you try to issue a FETCH statement it will return 0 or -1 regardless of the number of rows in the result set:

@@fetch_status = -9 value is not visible after performing fetch operation

For more information about system dynamic management views in SQL Server and sys.dm_exec_cursors, you can refer to the following links:

Also, you can refer to the following Stackoverflow.com posts for more information about @@FETCH_STATUS:

Conclusion

In this article, we described briefly SQL cursor database object by illustrating the different phases in this object life cycle. In addition, we explained the @@FETCH_STATUS system function that is used to check the status of the FETCH statement of the cursor and we illustrated the different values that it returns by providing some scenarios that can yield for each value.

Hadi Fadlallah
182 Views