ALAGESAN PERIYASAMY's BLOG: Built-in Functions

A cursor allows looping through a record set and performing a certain operation on each record within the set. SQL Server supports three functions that can help you while working with cursors: @@FETCH_STATUS, @@CURSOR_ROWS and CURSOR_STATUS. Cursor functions are non-deterministic.

A cursor life cycle can be described as follows:

Cursor is declared using the DECLARE CURSOR statement. This statement creates a cursor within SQL Server memory
Cursor is activated using OPEN CURSOR statement. At this point you can populate the cursor with a record set.
Data is retrieved from the cursor using the FETCH keyword.
A WHILE loop is executed within the cursor to perform some operation with the rows in the cursor with the condition that the FETCH command is successful.
Cursor is deactivated using CLOSE CURSOR statement. At this point you can't populate the cursor with additional rows. Nor can you work with rows within the cursor. However, you can re-open the cursor with OPEN CURSOR statement and perform additional work with the cursor.
The cursor is destroyed using DEALLOCATE CURSOR statement. Once the cursor is de-allocated it cannot be reopened.

@@FETCH

The most commonly used cursor function is @@FETCH_STATUS. This function determines whether FETCH keyword has successfully retrieved a row from the current cursor.

@@FETCH_STATUS can take one of the three values:

@@FETCH_STATUS value	Meaning
0	Successful fetch of a row within a cursor
-1	Fetch has failed. This could mean that the cursor has reached the beginning (or end) of the record set. This could also mean that we attempted retrieving a record that does not exist. For instance, if you attempt to grab 51st record within a cursor that has 50 records fetch status will be 1.
-2	The fetched row is missing. This means the record you're trying to FETCH has been deleted or its key has been updated since you have opened the cursor.

For example, the following cursor is populated with the top 5 customer names. While the cursor fetches rows successfully the @@FETCH_STATUS is 0. Once we get to the end of the result set @@FETCH_STATUS becomes -1:

DECLARE @customer_full_name VARCHAR(85)
DECLARE customer_cursor CURSOR FOR  SELECT TOP 5 FirstName + ' ' + MiddleName + ' ' + LastName FROM dimCustomer  
OPEN customer_cursor  
FETCH NEXT FROM customer_cursor INTO @customer_full_name
WHILE @@FETCH_STATUS = 0  
BEGIN   -- typically you'd do some row-based operation here  
FETCH NEXT FROM customer_cursor INTO @customer_full_name 
SELECT @@FETCH_STATUS AS fetch_status  
END 
CLOSE customer_cursor  
DEALLOCATE customer_cursor

Results:

fetch_status  
------------  
0
fetch_status  
------------  
0
fetch_status  
------------  
0
fetch_status  
------------  
0
fetch_status  
------------  
-1

@@CURSOR_ROWS

@@CURSOR_ROWS function returns the number of rows in the cursor which was opened last on the current connection. This means that if you have 3 cursors open @@CURSOR_ROWS will return the number of rows in the 3rd cursor. @@CURSOR_ROWS can take the following values:

@@CURSOR_ROWS value	Meaning
-m	Cursor is being populated asynchronously. "M" is the value of records in the record set.
-1	The cursor is DYNAMIC; that means, it reflects the changes to the data within the cursor. Therefore the number of rows can change due to addition or deletion of rows in the underlying tables. DYNAMIC cursors always return 1 as value of @@CURSOR_ROWS.
0	This can mean one of the following: Cursor has not been opened Cursor has no rows Cursor has been closed
N	Number of rows in the cursor record set. N is reported after the cursor has been fully populated

The following example shows you how @@CURSOR_ROWS value changes during the lifetime of the cursor:

DECLARE  @last_name  VARCHAR(20),
@first_name VARCHAR(20)
 
DECLARE MY_CURSOR CURSOR  FOR
SELECT TOP 3 LASTNAME,
FIRSTNAME
FROM   DIMCUSTOMER
 
SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows'
 
OPEN MY_CURSOR
 
FETCH NEXT FROM MY_CURSOR
INTO @last_name,
@first_name
 
SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows'
 
WHILE @@FETCH_STATUS = 0
BEGIN-- typically you'd do some row-based operation here  
FETCH NEXT FROM MY_CURSOR
INTO @last_name,
@first_name
END
 
CLOSE MY_CURSOR
 
SELECT 'cursor has ' + CAST(@@CURSOR_ROWS AS VARCHAR) + ' rows'
 
DEALLOCATE MY_CURSOR

Results:

----------------------------------------------  
cursor has 0 rows 
----------------------------------------------  
cursor has 3 rows 
----------------------------------------------  
cursor has 0 rows

If the same cursor is executed with DYNAMIC keyword against the entire DimCustomer table within Adventure Works database the results would show "cursor has -1 rows" while the cursor was being searched.

CURSOR_STATUS

The CURSOR_STATUS function can be used effectively within a stored procedure that calls another stored procedure, which returns an output parameter of CURSOR data type. This function can be used with local or global cursors and determines whether or not the stored procedure has returned a cursor with a result set. The syntax is:

1.CURSOR_STATUS( 'local' or 'global', cursor name)

1.CURSOR_STATUS ('variable', cursor variable name)

LOCAL or GLOBAL keywords allow you to specify the cursor scope; VARIABLE keyword specifies that CURSOR_STATUS function should examine a cursor variable.

In order to use the CURSOR data type as an output parameter, you must specify VARYING keyword along with OUTPUT within the CREATE PROCEDURE statement. The following example creates a procedure that returns a cursor as an output parameter:

CREATE PROCEDURE RETURN_CUSTOMER_NAMES(
@last_name VARCHAR(50),
@my_cursor CURSOR VARYING OUTPUT)
AS
BEGIN
SET NOCOUNT ON
 
SET @my_cursor =   CURSOR STATIC FOR   SELECT LastName, FirstName FROM DimCustomerWHERE LastName = @last_name
 
OPEN @my_cursor
END

Next, you can call this procedure from another procedure with a particular last name. The CURSOR_STATUS function will let you determine whether the cursor returned from the first procedure contains any rows, as follows:

CREATE PROCEDURE CALL_THE_OTHER_PROCEDURE
@last_name VARCHAR(50)
AS
SET NOCOUNT ON
 
DECLARE  @first_name VARCHAR(20)
 
DECLARE  @my_cursor CURSOR
 
/* now call the procedure returning a cursor output parameter */
EXECUTE RETURN_CUSTOMER_NAMES
@last_name ,
@my_cursor OUTPUT
 
IF CURSOR_STATUS('variable','@my_cursor') = 0
BEGIN
PRINT 'no records found'
 
RETURN
END
ELSE
BEGIN
FETCH NEXT FROM @my_cursor
INTO @last_name,
@first_name
 
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @last_name,
@first_name
 
FETCH NEXT FROM @my_cursor
INTO @last_name,
@first_name
END
 
CLOSE @my_cursor
 
DEALLOCATE @my_cursor
END

Once the two procedures are created you can execute the caller procedure with the last name "Huang" as follows:

1.EXECUTE call_the_other_procedure 'huang'

Results (abbreviated):

-------------------------------------------------- --------------------  
Huang  Eugene  
-------------------------------------------------- --------------------  
Huang  Erica  
-------------------------------------------------- --------------------  
Huang  Alejandro  
-------------------------------------------------- --------------------  
Huang  Shannon

The following table summarizes the values returned by CURSOR_STATUS function:

CURSOR_STATUS value	Meaning for variable	Meaning for cursor name
1	Cursor is open and has at least 1 row DYNAMIC cursors return 1 even if they contain no rows	Cursor is open and has at least 1 row DYNAMIC cursors return 1 even if they contain no rows
0	Cursor is open but has no rows	Cursor has no rows
-1	The cursor is closed	The cursor is closed
-2	Cursor wasn't returned by the called procedure OR the cursor was deallocated prior to being assigned to this variable	The value of 2 is never returned by CURSOR_STATUS if you refer to cursor name
-3	Cursor variable with the specified name does not exist OR the variable exists, but it hasn't been assigned to a cursor returned from the called procedure	Cursor with the specified name does not exist

The following example shows how the values returned by the CURSOR_STATUS function change during the cursor's lifecycle:

SET NOCOUNT ON 
 
SELECT 'cursor hasn''t been declared; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)
 
DECLARE  @last_name  VARCHAR(20),
@first_name VARCHAR(20)
 
DECLARE MY_CURSOR CURSOR GLOBAL FOR
SELECT LASTNAME,
FIRSTNAME
FROM   DIMCUSTOMER
WHERE  LASTNAME = 'white'
 
SELECT 'cursor is declared; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)
 
OPEN MY_CURSOR
 
SELECT 'cursor is opened; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)
 
FETCH NEXT FROM MY_CURSOR
INTO @last_name,
@first_name
 
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @last_name,
@first_name
 
FETCH NEXT FROM MY_CURSOR
INTO @last_name,
@first_name
END
 
CLOSE MY_CURSOR
 
SELECT 'cursor is closed; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)
 
DEALLOCATE MY_CURSOR
 
SELECT 'cursor is deallocated; the status is: ' + CAST(CURSOR_STATUS('global','my_cursor') AS VARCHAR)

Results (abbreviated):

--------------------------------------------------------------------------  
cursor hasn't been declared; the status is: -3  
-----------------------------------------------------------------  
cursor is declared; the status is: -1  
---------------------------------------------------------------  
cursor is opened; the status is: 1 
-------------------- --------------------  
WhiteIan  
---------------------------------------------------------------  
cursor is closed; the status is: -1  
--------------------------------------------------------------------  
cursor is deallocated; the status is: -3

ALAGESAN PERIYASAMY's BLOG

Friday, 19 August 2011

Built-in Functions - Cursor Functions

@@FETCH

@@CURSOR_ROWS

CURSOR_STATUS

No comments:

Post a Comment

Blog Archive

About Me