Sql - Call Stored Procedure for each record

sql

cursor-management

performance-optimization

best-practices

byNikita Barsukov·Mar 7, 2025

DECLARE @ID INT; // Initializing our variable, @ID.
DECLARE cur CURSOR FOR SELECT ID FROM Records; // Creating a cursor named 'cur'.
OPEN cur; // Opening the cursor for business!

FETCH NEXT FROM cur INTO @ID; // Fetching the first record from the cursor.
WHILE @@FETCH_STATUS = 0 // We loop while FETCH_STATUS is equal to 0, indicating a successful fetch.
BEGIN
    EXEC ProcedureName @ID; // Calling our stored procedure with the current ID.
    FETCH NEXT FROM cur INTO @ID; // Time for the next record!
END

CLOSE cur; // Closing time at the cursor bar!
DEALLOCATE cur; // Saying goodbye to the cursor.

This script loops through each record with a cursor, using the ID from Records, and calls the stored procedure ProcedureName with the ID as a parameter. Swap out ProcedureName, ID, and Records for your names.

Pro tips for cursor management

A cursor is your friend. Treat it well:

Ensure proper treatment – declare, open, fetch, close and deallocate – when playing with cursors.
Fetch efficiently: Incorporate necessary tables in your SELECT statement to retrieve your IDs. Remember, time is money.
Fetch status check: Use @@FETCH_STATUS in the loop. Is your cursor fetch successful? It better be.
Beware of the data type! Ensure your cursor variable has a suitable data type.

Better strategies for large records

When your cursor starts to crawl on large datasets, consider:

Temp tables and loops: Goodbye cursors?

CREATE TABLE #IDs (ID INT);
INSERT INTO #IDs SELECT ID FROM Records;

WHILE EXISTS (SELECT 1 FROM #IDs)
BEGIN
    SELECT TOP 1 @ID = ID FROM #IDs;
    EXEC ProcedureName @ID;
    DELETE FROM #IDs WHERE ID = @ID;
END

DROP TABLE #IDs;

Put your IDs in a temporary table, and loop through that. This method allows for better performance and easier record tracking.

Table-valued functions (TVFs): Set-based operations

Imagine replacing your stored procedure with a table-valued function (TVF) instead. TVFs let you use CROSS APPLY, which can drastically step up operation efficiency.

Iterate efficiently

Prefer a Max/Delete (or Min/delete) iteration approach.
EXISTS function: Keep a check on remaining records without scanning the whole temporary table.

Performance considerations

Performance is key in handling large datasets:

Ensure your queries are faster than Usain Bolt by optimizing them and indexing properly.
Try to dodge cursors if a set-based operation is possible – it's all about saving time (and resources).
Less is more: minimize iterations. If the operation sequence matters, be wise in ordering records.

Robust error handling is key

BEGIN TRY
    -- Cursor or loop code here
END TRY
BEGIN CATCH
    -- Handle errors and rollback transactions or log them as necessary
END CATCH

A robust error handling mechanism within the stored procedure calls ensures smooth operations and keeps the drama of exceptions away.

Simplify & maintain

Although complexity might seem tempting, it is often helpful to stick to simplicity and maintainability:

Make your code as readable as a children's book - this helps future maintainers, who send virtual hugs your way.
Adhere to best practices in coding and commenting. It's the tie that binds the development community together.

Optimize cursor usage

Deals with scale

Be aware of transaction logs for large datasets. They can fill up faster than a water balloon under Niagara Falls. Batch operation or limiting the loop iterations may be required.