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
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;
Oracle
Explicit cursor
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;
Implicit Cursor
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;
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 [email protected]
Last updated