Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Cursor FunctionsRSS Feeds

CURSOR_STATUS Function

Posted By: Sarita Patel     Category: SQL Server     Views: 4291

This article explains about CURSOR_STATUS function in sql server with examples.

CURSOR_STATUS function allows the caller of a stored procedure to determine whether or not the procedure has returned a cursor and result set for a given parameter.


Syntax of CURSOR_STATUS Function :

CURSOR_STATUS 
    ( 
        { 'local' , 'cursor_name' } 
        | { 'global' , 'cursor_name' } 
        | { 'variable' , 'cursor_variable' } 
    )

local specifies that the source of the cursor is a local cursor name.

cursor_name is a name of the cursor.  

global specifies that the source of the cursor is a global cursor name. 

variable specifies that the source of the cursor is a local variable name. 

cursor_variable is the name of the cursor variable. A cursor variable must be defined using the cursor data type.

Return type of CURSOR_STATUS function is smallint.

The following table summarizes the values returned by CURSOR_STATUS function:

Return Value

Cursor Name

Cursor Variable

1

The result set of the cursor has at least one row and:

For insensitive and keyset cursors, the result set has at least one row.

For dynamic cursors, the result set can have zero, one, or more rows.

The cursor allocated to this variable is open and:

For insensitive and keyset cursors, the result set has at least one row.

For dynamic cursors, the result set can have zero, one, or more rows.

0

The result set of the cursor is empty.*

The cursor allocated to this variable is open, but the result set is definitely empty.*

-1

The cursor is closed.

The cursor allocated to this variable is closed.

-2

Not applicable.

Can be:

No cursor was assigned to this OUTPUT variable by the previously called procedure.

A cursor was assigned to this OUTPUT variable by the previously called procedure, but it was in a closed state upon completion of the procedure. Therefore, the cursor is deallocated and not returned to the calling procedure.

There is no cursor assigned to a declared cursor variable.

-3

A cursor with the specified name does not exist.

A cursor variable with the specified name does not exist, or if one exists it has not yet had a cursor allocated to it.





Examples of CURSOR_STATUS Function :

Example 1 : Use of CURSOR_STATUS function

The following example creates a procedure that returns a cursor as an output parameter:

CREATE PROCEDURE GET_CUSTOMER_NAMES(
                @last_name VARCHAR(50),
                @my_cursor CURSOR VARYING OUTPUT)
AS
  BEGIN
    SET NOCOUNT ON
    
    SET @my_cursor =   CURSOR STATIC FOR   
SELECT ContactName FROM Customers 
WHERE ContactName LIKE '%' + @last_name + '%'
    
    OPEN @my_cursor
  END


Now 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 below:

CREATE PROCEDURE CALL_PROCEDURE
                @last_name VARCHAR(50)
AS
  SET NOCOUNT ON
  
  DECLARE  @full_name VARCHAR(20)
  
  DECLARE  @my_cursor CURSOR
  
  /* now call the procedure returning a cursor output parameter */
  EXECUTE GET_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,
           @full_name
      
      WHILE @@FETCH_STATUS = 0
        BEGIN
          SELECT @last_name,
                 @full_name
          
          FETCH NEXT FROM @my_cursor
          INTO @last_name,
               @full_name
        END
      
      CLOSE @my_cursor
      
      DEALLOCATE @my_cursor
    END

Now you can execute the caller procedure with the last name "Moroni" as follows:

EXECUTE CALL_PROCEDURE 'Moroni'

Output
Maurizio Moroni



  
Share: 

 
 
 

Didn't find what you were looking for? Find more on CURSOR_STATUS Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of CURSOR_STATUS Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!