Cursor In Stored Procedure

SQL Server Technical Debt: Cursor In Stored Procedure

Here is an example of a bad stored procedure written in T-SQL. There are many reasons that it is considered bad one reason that it is considered bad is that it is using a cursor.

CREATE PROCEDURE [dbo].[Bad Sproc]
AS
BEGIN

 DECLARE MY_cursor Cursor
 FOR
 SELECT OBJECT_NAME(sc.object_id) AS TableName,
 name AS ColumnName
 FROM sys.columns sc
 WITH (NOLOCK);

Open MY_cursor;

DECLARE @tableName as varchar(1024);
 DECLARE @columnName as varchar(1024);

 FETCH NEXT FROM MY_cursor INTO @tableName, @columnName;
 While (@@FETCH_STATUS <> -1)
 BEGIN
 print @tableName;
 FETCH NEXT FROM MY_cursor INTO @tableName, @columnName;
 END
 CLOSE MY_cursor;
 DEALLOCATE MY_cursor;

return;
END

There is cases is that it absolutely appropriate to use cursors in stored procedures, but it has been my experience that cursors hiding in stored procedures often cause scalability and performance problems.

Do you agree?

If you don’t agree this cursors in stored procedures being considered as technical debt, that is just fine, in the Database Health Reports settings dialog you can turn the check for cursors in stored procedures off. As long as you have coding standards that state one opinion or another on cursors as being your standard, then you are doing better than most.

A good coding standard can over-ride many of the items that may be considered as technical debt.

See Also:

SQL Server Technical Debt