Cursors - Oracle to SQL Server Migration

In Oracle and SQL Server you can declare a cursor, use OPEN, FETCH and CLOSE statements, but besides syntax there are some semantic differences.

In Oracle cursors are always local i.e visible only within the scope where they are declared. In SQL Server a cursor can be declared as LOCAL or GLOBAL.

By default, cursors are global in SQL Server i.e you can reference a cursor outside unless it is explicitly deallocated using DEALLOCATE statement.

Use the following query to check whether cursors are local or global by default in SQL Server (0 means cursors are global, 1 returned for local):

SQL Server:

  -- Change db_name to specify your database name
  SELECT DATABASEPROPERTYEX('db_name', 'IsLocalCursorsDefault')
  # 0

Consider the following sample procedure that uses a cursor (do not pay attention to its logic, it is just to demonstate cursor declaration and operations):

Oracle:

  CREATE OR REPLACE PROCEDURE sp_date
  IS
    v_date DATE;
    CURSOR cur IS SELECT SYSDATE FROM dual;
 
  BEGIN  
    OPEN cur;
    FETCH cur INTO v_date;
    CLOSE cur;    
 
    DBMS_OUTPUT.PUT_LINE(v_date);
  END;
  /

You can invoke this procedure multiple times, and it successfully declares, opens and closes the cursor each time:

Oracle:

  CALL sp_date();
  # 09-OCT-13
 
  CALL sp_date();
  #09-OCT-13

Now let's see what happens if you convert this procedure to SQL Server and use CLOSE statement without DEALLOCATE statement:

SQL Server:

  CREATE PROCEDURE sp_date
  AS
    DECLARE @v_date DATE;
    DECLARE cur CURSOR FOR SELECT GETDATE();
 
    OPEN cur;
    FETCH cur INTO @v_date;
    CLOSE cur;    
 
    PRINT @v_date;
  GO

Without the DEALLOCATE statement, the second and any subsequent call to this procedure returns 'cursor already exists' error:

SQL Server:

  EXEC sp_date
  # 2013-10-09
 
  EXEC sp_date
  # Msg 16915, Level 16, State 1, Procedure sp_date, Line 4
  # A cursor with the name 'cur' already exists.
  # 2013-10-09

Note that only the DECLARE statement fails, the procedure continues its execution and opens the previously declared cursor. A more serious problem arises if you have another procedure that uses a cursor with the same name, but different definition.

To release the cursor and to allow calling the procedure multiple times in a session, you use can use the DEALLOCATE statement in SQL Server:

SQL Server:

  -- Cursor still GLOBAL but DEALLOCATE is used now
  CREATE PROCEDURE sp_date2
  AS
    DECLARE @v_date DATE;
    DECLARE cur CURSOR FOR SELECT GETDATE();
 
    OPEN cur;
    FETCH cur INTO @v_date;
    CLOSE cur;  
    DEALLOCATE cur;    
 
    PRINT @v_date;
  GO

Now you can successfully call the procedure multiple times:

SQL Server:

  EXEC sp_date2
  # 2013-10-09
 
  EXEC sp_date2
  # 2013-10-09

In the previous example, the cursor was declared as global (it is default), and although the DEALLOCATE statement released the reference to cursor in this simple example, it maybe not enough in some cases, for example:

• There is a nested stored procedure that declares a cursor with the same name.
• An exception that forces to terminate the procedure execution leaving the cursor not deallocated.

To solve these issues specify specify LOCAL in the cursor declaration explicitly:

SQL Server:

  -- Cursor is LOCAL now
  CREATE PROCEDURE sp_date3
  AS
    DECLARE @v_date DATE;
    DECLARE cur CURSOR LOCAL FOR SELECT GETDATE();
 
    OPEN cur;
    FETCH cur INTO @v_date;
    CLOSE cur;  
    DEALLOCATE cur;    
 
    PRINT @v_date;
  GO

Note that when a cursor is declared as LOCAL it is deallocated automatically when the procedure completes, but you can still use the DEALLOCATE statement too.

Note that SQL Server does not allow you to declare GLOBAL cursor in a user-defined function, and cursor are always LOCAL:

SQL Server:

  CREATE FUNCTION fn_date() RETURNS DATE
  AS
  BEGIN
    DECLARE @v_date DATE;
    DECLARE cur CURSOR FOR SELECT GETDATE();
 
    OPEN cur;
    FETCH cur INTO @v_date;
    CLOSE cur;    
 
    RETURN @v_date;
  END
  GO

You can call a function multiple times without errors even if it does not use LOCAL and DEALLOCATE:

SQL Server:

  SELECT dbo.fn_date()
  # 2013-10-09
 
  SELECT dbo.fn_date()
  # 2013-10-09

Oracle allows you to declare a cursor with formal parameters. Then when you open the cursor you can specify the actual values to be used in the cursor.

Let's create a sample table with data:

Oracle:

  CREATE TABLE colors (name VARCHAR2(70));
 
  INSERT INTO colors VALUES ('Red');
  INSERT INTO colors VALUES ('White');
  INSERT INTO colors VALUES ('Blue');

Now let's create a sample procedure that uses a cursor with parameters:

Oracle:

  CREATE OR REPLACE PROCEDURE sp_find_colors
  IS
    v_name VARCHAR2(70);
    CURSOR cur(p_name VARCHAR2) IS SELECT name FROM colors WHERE name = p_name;
 
  BEGIN  
    OPEN cur('Red');
    FETCH cur INTO v_name;
    CLOSE cur;    
 
    DBMS_OUTPUT.PUT_LINE('Found: ' || v_name);
 
    OPEN cur('White');
    FETCH cur INTO v_name;
    CLOSE cur;    
 
    DBMS_OUTPUT.PUT_LINE('Found: ' || v_name);
  END;
  /

Now when you call this procedure it outputs 'Red' and 'White':

Oracle:

  CALL sp_find_colors();
  # Found: Red
  # Found: White

SQL Server does not support cursors with parameters, so you have to use variables or pass values explicitly.

Note that SQL Server gets values of variables during the cursor declaration, not during execution of OPEN cursor statement.

SQL Server:

  CREATE PROCEDURE sp_find_colors
  AS
    DECLARE @v_name VARCHAR(70);
 
    -- @p_name is NULL, and this NULL value will be used in cursor
    DECLARE @p_name VARCHAR(70);
    DECLARE cur CURSOR LOCAL FOR SELECT name FROM colors WHERE name = @p_name;
 
    -- This assignment will not have any affect on @p_name used in the cursor
    SET @p_name = 'Red';
 
    OPEN cur;
    FETCH cur INTO @v_name;
    CLOSE cur;    
 
    PRINT 'Found: ' + ISNULL(@v_name, '');
 
    -- This assignment also will not have any affect on @p_name used in the cursor
    SET @p_name = 'White';
 
    OPEN cur;
    FETCH cur INTO @v_name;
    CLOSE cur;    
    DEALLOCATE cur;
 
    PRINT 'Found: ' + ISNULL(@v_name, '');
  GO

Now when you call this procedure it outputs empty values:

SQL Server:

  EXEC sp_find_colors;
  # Found: 
  # Found:

The reason is that te value of @p_name is NULL during the cursor declaration, and this NULL value is used in each OPEN statement, not the new value that was assigned just before openning the cursor.

To solve this problem you have to execute DECLARE CURSOR statement for each new parameter value:

SQL Server:

  CREATE PROCEDURE sp_find_colors
  AS
    DECLARE @v_name VARCHAR(70);
    DECLARE @p_name VARCHAR(70);
 
    SET @p_name = 'Red';
 
    DECLARE cur CURSOR LOCAL FOR SELECT name FROM colors WHERE name = @p_name;
    OPEN cur;
    FETCH cur INTO @v_name;
    CLOSE cur;    
    DEALLOCATE cur;
 
    PRINT 'Found: ' + ISNULL(@v_name, '');
 
    SET @p_name = 'White';
 
    DECLARE cur CURSOR LOCAL FOR SELECT name FROM colors WHERE name = @p_name;
    OPEN cur;
    FETCH cur INTO @v_name;
    CLOSE cur;    
    DEALLOCATE cur;
 
    PRINT 'Found: ' + ISNULL(@v_name, '');
  GO

Now the procedure outputs correct values:

SQL Server:

  EXEC sp_find_colors;
  # Found: Red
  # Found: White

For more information, see:

• Oracle to SQL Server Migration