MSCCP0010

The statement below has usages of nested cursors

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is used to indicate that the statement has usages of nested cursors. A cursor is a database object that allows traversing and manipulating result sets in a database. When nested cursors are used, it means that one cursor is nested inside the loop of another cursor.

The use of nested cursors can have a significant impact on performance, especially with large result sets. Each cursor operation requires a round trip to the database server, which can increase overhead and slow down execution.

Code examples

SQL Server

Input

CREATE OR ALTER PROCEDURE procedureSample
AS
BEGIN
  DECLARE
    @outer_category_id INT,
    @outer_category_name NVARCHAR(50),
    @inner_product_name NVARCHAR(50);

  -- Define the outer cursor
  DECLARE outer_cursor CURSOR FOR 
    SELECT category_id, category_name FROM categories;

  -- Open the outer cursor
  OPEN @outer_cursor;

  -- Fetch the first row from the outer cursor
  FETCH NEXT FROM outer_cursor INTO @outer_category_id, @outer_category_name;

  -- Start the outer loop
  WHILE @@FETCH_STATUS = 0
  BEGIN

    PRINT 'Category: ' + @outer_category_name;
	
    -- Define the inner cursor
    DECLARE inner_cursor CURSOR FOR
      SELECT product_name FROM products WHERE category_id = @outer_category_id;
    
    -- Open the inner cursor
    OPEN inner_cursor;
	FETCH NEXT FROM inner_cursor INTO @inner_product_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN
      PRINT 'Product: ' + @inner_product_name + ' Category: ' + CAST(@outer_category_id AS NVARCHAR(10));

      -- Fetch the next row from the inner cursor
      FETCH NEXT FROM inner_cursor INTO @inner_product_name;
    END;

    -- Close the inner cursor
    CLOSE inner_cursor;
    DEALLOCATE inner_cursor;

    -- Fetch the next row from the outer cursor
    FETCH NEXT FROM outer_cursor INTO @outer_category_id, @outer_category_name;
  END;

  -- Close the outer cursor
  CLOSE outer_cursor;
  DEALLOCATE outer_cursor;
  
END;

Output

CREATE OR REPLACE PROCEDURE procedureSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    OUTER_CATEGORY_ID INT;
    OUTER_CATEGORY_NAME VARCHAR(50);
    INNER_PRODUCT_NAME VARCHAR(50);
    /*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
    outer_cursor CURSOR
    FOR
      SELECT
        category_id,
        category_name
      FROM
        categories;
    /*** MSC-ERROR - MSCEWI4038 - SNOWFLAKE SCRIPTING CURSOR ROWS ARE NOT MODIFIABLE ***/
    inner_cursor CURSOR
    FOR
      SELECT
        product_name
      FROM
        products
      WHERE
        category_id = :OUTER_CATEGORY_ID;
  BEGIN
     
     
    --** MSC-WARNING - MSCCP0010 - THIS STATEMENT HAS USAGES OF NESTED CURSORS. **
    OPEN OUTER_CURSOR;
    FETCH
      outer_cursor
      INTO
      :OUTER_CATEGORY_ID,
      :OUTER_CATEGORY_NAME;
      WHILE (:FETCH_STATUS = 0) LOOP
-- ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR PRINT IS PLANNED TO BE DELIVERED IN THE FUTURE **
--      PRINT 'Category: ' + @outer_category_name;
       
      OPEN inner_cursor;
      --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
      FETCH
        inner_cursor
      INTO
        :INNER_PRODUCT_NAME;
      WHILE (:FETCH_STATUS = 0) LOOP
-- ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR PRINT IS PLANNED TO BE DELIVERED IN THE FUTURE **
--        PRINT 'Product: ' + @inner_product_name + ' Category: ' + CAST(@outer_category_id AS NVARCHAR(10));
        --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        FETCH
          inner_cursor
        INTO
          :INNER_PRODUCT_NAME;
      END LOOP;
      CLOSE inner_cursor;
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'DEALLOCATE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      DEALLOCATE inner_cursor
                             ;
      --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
      FETCH
        outer_cursor
      INTO
        :OUTER_CATEGORY_ID,
        :OUTER_CATEGORY_NAME;
      END LOOP;
      CLOSE outer_cursor;
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'DEALLOCATE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      DEALLOCATE outer_cursor                             ;
  END;
$$;

Oracle

Explicit cursor

Input

CREATE OR REPLACE PROCEDURE procedureSample AS
BEGIN
DECLARE
  CURSOR outer_cursor IS
    SELECT category_id, category_name FROM categories;

  CURSOR inner_cursor (p_category_id NUMBER) IS
    SELECT product_name FROM products WHERE category_id = p_category_id;

  outer_category_id categories.category_id%TYPE;
  outer_category_name categories.category_name%TYPE;
  inner_product_name products.product_name%TYPE;
BEGIN

  OPEN outer_cursor;
  FETCH outer_cursor INTO outer_category_id, outer_category_name;

  LOOP
    EXIT WHEN outer_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Category: ' || outer_category_name);

    OPEN inner_cursor(outer_category_id);
    LOOP
        FETCH inner_cursor INTO inner_product_name;
        EXIT WHEN inner_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Product: ' || inner_product_name || ' Category: ' || outer_category_id);
    END LOOP;
    CLOSE inner_cursor;

    FETCH outer_cursor INTO outer_category_id, outer_category_name;
  END LOOP;

  CLOSE outer_cursor;
END;
END;

Output

CREATE OR REPLACE PROCEDURE procedureSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    DECLARE
      outer_cursor CURSOR
      FOR
        SELECT category_id, category_name FROM
          categories;
      inner_cursor CURSOR
      FOR
        SELECT product_name FROM
          products
        WHERE category_id = ?;
      outer_category_id VARIANT /*** MSC-WARNING - MSCEWI3129 - TYPE ATTRIBUTE 'categories.category_id%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/;
      outer_category_name VARIANT /*** MSC-WARNING - MSCEWI3129 - TYPE ATTRIBUTE 'categories.category_name%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/;
      inner_product_name VARIANT /*** MSC-WARNING - MSCEWI3129 - TYPE ATTRIBUTE 'products.PRODUCT_NAME%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/;
    BEGIN
      --** MSC-WARNING - MSCCP0010 - THIS STATEMENT HAS USAGES OF NESTED CURSORS. **
      OPEN outer_cursor USING ('DEFAULT VALUE NOT FOUND');
      FETCH outer_cursor INTO
        :outer_category_id,
        :outer_category_name;
      LOOP
        IF (outer_category_id IS NULL) THEN
          EXIT;
        END IF;
        /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. ***/
        /*** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. ***/
        CALL DBMS_OUTPUT.PUT_LINE('Category: ' || NVL(:outer_category_name :: STRING, ''));
        OPEN inner_cursor USING (:outer_category_id);
        LOOP
          --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
          FETCH inner_cursor INTO
            :inner_product_name;
          IF (inner_product_name IS NULL) THEN
            EXIT;
          END IF;
          /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. ***/
          /*** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. ***/
          CALL DBMS_OUTPUT.PUT_LINE('Product: ' || NVL(:inner_product_name :: STRING, '') || ' Category: ' || NVL(:outer_category_id :: STRING, ''));
        END LOOP;
        CLOSE inner_cursor;
        --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
        FETCH outer_cursor INTO
          :outer_category_id,
          :outer_category_name;
      END LOOP;
      CLOSE outer_cursor;
    END;
  END;
$$;

Implicit Cursor

Input

CREATE OR REPLACE PROCEDURE procedureSample AS
BEGIN
DECLARE
   inner_category_id categories.category_name%TYPE;
   inner_product_name products.product_name%TYPE;
   inner_cursor SYS_REFCURSOR;
BEGIN
   FOR outer_cursor IN (SELECT category_id, category_name FROM categories)
   LOOP
      OPEN inner_cursor
       FOR SELECT product_name, category_id FROM products WHERE category_id = outer_cursor.category_id;
      LOOP
         FETCH inner_cursor INTO inner_product_name, inner_category_id;
         EXIT WHEN inner_cursor%NOTFOUND;
         dbms_output.put_line( 'Category id: '|| outer_cursor.category_id);
         dbms_output.put_line('Product name: ' || inner_product_name);
      END LOOP;
      CLOSE inner_cursor;
   END LOOP;
END;
END;

Output

CREATE OR REPLACE PROCEDURE procedureSample ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  BEGIN
    DECLARE
       inner_category_id VARIANT /*** MSC-WARNING - MSCEWI3129 - TYPE ATTRIBUTE 'categories.category_name%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/;
       inner_product_name VARIANT /*** MSC-WARNING - MSCEWI3129 - TYPE ATTRIBUTE 'products.PRODUCT_NAME%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/;
      inner_cursor_res RESULTSET /*** MSC-WARNING - MSCEWI1036 - SYS_REFCURSOR DATA TYPE CONVERTED TO RESULTSET ***/;
    BEGIN
      LET temporary_for_cursor_0 CURSOR
      FOR
        (SELECT category_id, category_name FROM
            categories
        );
      --** MSC-WARNING - MSCCP0010 - THIS STATEMENT HAS USAGES OF NESTED CURSORS. **
      /*** MSC-WARNING - MSCCP0003 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP ***/
      FOR outer_cursor IN temporary_for_cursor_0 DO
        LET inner_cursor CURSOR
        FOR
          SELECT product_name, category_id FROM
            products
          WHERE category_id = outer_cursor.category_id;
        OPEN inner_cursor;
        LOOP
          --** MSC-WARNING - MSCCP0006 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
          FETCH inner_cursor INTO
            :inner_product_name,
            :inner_category_id;
          IF (inner_product_name IS NULL) THEN
            EXIT;
          END IF;
          /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. ***/
          /*** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. ***/
          CALL DBMS_OUTPUT.PUT_LINE( 'Category id: ' || NVL(outer_cursor.category_id :: STRING, ''));
          /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'DBMS_OUTPUT.PUT_LINE' INSERTED. ***/
          /*** MSC-WARNING - MSCEWI3115 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE. ***/
          CALL DBMS_OUTPUT.PUT_LINE('Product name: ' || NVL(:inner_product_name :: STRING, ''));
        END LOOP;
             CLOSE inner_cursor;
      END FOR;
    END;
  END;
$$;

Recommendations

• Avoid them whenever possible due to their potential impact on performance and code complexity.

• Remove the use of nested cursors, instead, you can use SQL functions, joins, subqueries, window functions, common table expressions (CTEs), recursive queries, and other features to process bulk data and avoid the need for nested cursors.

• If you need more support, you can email us at [e-mail]