SQL Server › Cursors

In what rare situations might using a cursor still be a reasonable choice despite the general recommendation to avoid them?

Advanced

Answer

Cursors can be reasonable when you need to call an external process, like a stored procedure that sends an email, once for each row individually, when performing complex administrative tasks such as looping through all databases on a server to run maintenance commands, or when a truly row dependent calculation cannot realistically be expressed as a single set based statement.

DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE state = 0;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DbName;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC ('DBCC CHECKDB (' + @DbName + ')');
  FETCH NEXT FROM db_cursor INTO @DbName;
END

Real-world example A database administrator uses a cursor to loop through every database on a server and run an integrity check command against each one individually, since this kind of administrative task naturally requires acting on one database at a time.

Common follow-ups: What administrative tasks commonly justify using a cursor?;Are there set based alternatives even for these administrative scenarios?

SQL Server Agent & Job Scheduling;Stored Procedures & Functions