Cursors in Stored Procedures

Stored Procedures: The Basics and Why Cursors Matter

A stored procedure is essentially a pre‑written query that you keep in the database so you can run it whenever you need. Think of it like a saved recipe: you have all the ingredients ready and you just need to give it the right parameters to produce the dish you want. The real power of a stored procedure comes from its ability to accept input parameters, which means the same code can be reused for many different scenarios.

Consider a frequent query like this:

Every time you run it you have to type out the entire statement, including the number of employees that may change from one run to the next. Instead, you can package this query into a stored procedure that takes the employee threshold as a parameter:

Now you can call it simply with:

and if you need to use 200 next time, you just pass a different value.

Stored procedures shine when you have logic that goes beyond a single SELECT or UPDATE. You can add conditions, join tables, handle errors, and even return multiple result sets. However, there are still situations where a plain UPDATE or SELECT isn't enough, especially when the logic you need to apply is row‑by‑row or requires state that changes as you process each record.

Enter the cursor. A cursor is a database object that lets you treat a set of rows like a table you can walk through one row at a time. You fetch a row, put its data into variables, do some calculations or checks, and then optionally write data back to the database. This pattern is useful when you need to perform complex updates that depend on the current state of the row or when you need to generate values that are not available through standard SQL expressions.

Because cursors bring a procedural flow to SQL, they can be less efficient than set‑based operations. They also consume more resources, especially if you leave them open for too long. Therefore, you should use them only when set‑based logic can't solve the problem cleanly. Below we will explore two typical use cases that justify the use of a cursor and then walk through a concrete example of inserting unique names from an external source into a table that may already contain data.

By understanding when and how to employ a cursor, you can avoid overcomplicating your stored procedures while still meeting business requirements that demand row‑by‑row processing. The key is to keep the cursor logic clear, limit its scope, and clean it up properly once the job is done.

When Cursors Become Essential

Set‑based operations are the bread and butter of SQL. When you write a query that updates or selects rows in bulk, the database engine can optimize the work, use indexes efficiently, and avoid unnecessary loops. In many scenarios, you can achieve the desired result with a single UPDATE statement that includes CASE expressions or joins.

However, there are legitimate reasons why you might need to break out of that world and walk through each row individually:

1. Complex Conditional Logic: Suppose you need to update a table based on a combination of fields that involves a series of nested conditions, or you need to compare the current row to data that exists elsewhere in the same table. Writing a single UPDATE that captures all the nuances can become unwieldy or impossible.
2. Generating Sequential Values: If you need to assign a unique identifier that depends on the current maximum value in the table but must also account for the order in which rows are processed, a cursor can help maintain the correct sequence without race conditions.
3. Data Migration or Integration Tasks: When moving data from an external system that may contain duplicates or incomplete information, you often need to examine each record, decide whether to insert it, update an existing row, or skip it entirely. A cursor gives you fine‑grained control over that decision‑making process.
4. Logging or Auditing: After processing each row, you might need to write a detailed log entry that records exactly what happened to that row. This level of granularity is easier to achieve inside a cursor loop.

   To decide whether a cursor is the right tool, ask yourself: can the same result be expressed in a set‑based query? If yes, go that route. If not, or if readability and maintainability become an issue, a cursor might be the better choice.

   When you do use a cursor, there are several patterns to keep in mind:

   • Declare only what you need. Define the cursor with the minimal set of columns required for the logic.
   • Open, fetch, process, and close. Keep the cursor open for as short a time as possible. Close and deallocate as soon as you finish.
   • Handle errors. Wrap the cursor logic in TRY/CATCH blocks so that you can roll back if something goes wrong.
   • Test with small data sets. Before deploying a cursor to production, run it on a subset to confirm it behaves as expected.

     Understanding these guidelines will help you write efficient, robust stored procedures that use cursors only when necessary.

     Step‑by‑Step: Building a Unique Name Loader with a Cursor

     Let’s walk through a practical example that showcases a common scenario: we have a table called People that stores unique names along with an id column. The People table might already contain rows, or it might be empty. Our goal is to pull distinct names from a pre‑defined source table that may contain duplicates, and insert only those names that do not already exist in People. In addition, we want to generate new id values that follow the current maximum value.

     Below is the stored procedure that accomplishes this task. We’ll dissect each part to understand why it’s written that way.

     Prompt

     CREATE PROCEDURE Unique_Name_Into_People</p> <p>AS</p> <p>BEGIN</p> <p> SET NOCOUNT ON;</p> <p> -- Variables that hold intermediate values</p> <p> DECLARE @name_exists INT;</p> <p> DECLARE @max_id INT;</p> <p> DECLARE @current_id INT;</p> <p> DECLARE @name_string VARCHAR(255);</p> <p> DECLARE @fetch_status INT;</p> <p> -- Define a cursor that returns each distinct name from the source table</p> <p> DECLARE distinctname CURSOR FOR</p> <p> SELECT DISTINCT name</p> <p> FROM Predefined_Table</p> <p> FOR READ ONLY;</p> <p> -- Open the cursor so that the SELECT statement is executed</p> <p> OPEN distinctname;</p> <p> -- Determine the starting value for @current_id</p> <p> IF EXISTS (SELECT 1 FROM People)</p> <p> BEGIN</p> <p> SELECT @max_id = MAX(id) FROM People;</p> <p> SET @current_id = @max_id;</p> <p> END</p> <p> ELSE</p> <p> BEGIN</p> <p> SET @current_id = 0;</p> <p> END</p> <p> -- Loop over each row returned by the cursor</p> <p> FETCH NEXT FROM distinctname INTO @name_string;</p> <p> WHILE @@FETCH_STATUS = 0</p> <p> BEGIN</p> <p> SET @current_id = @current_id + 1;</p> <p> -- Check if the name is already present</p> <p> SELECT @name_exists = COUNT(*)</p> <p> FROM People</p> <p> WHERE name = @name_string;</p> <p> IF @name_exists = 0</p> <p> BEGIN</p> <p> INSERT INTO People (id, name)</p> <p> VALUES (@current_id, @name_string);</p> <p> END</p> <p> ELSE</p> <p> BEGIN</p> <p> -- If the name exists, decrement the id counter</p> <p> SET @current_id = @current_id - 1;</p> <p> END</p> <p> FETCH NEXT FROM distinctname INTO @name_string;</p> <p> END</p> <p> -- Clean up</p> <p> CLOSE distinctname;</p> <p> DEALLOCATE distinctname;</p> <p>END

     Now let’s explain why the procedure is structured this way.

     First, we declare a handful of variables that will be reused throughout the procedure. @name_exists holds the result of a simple count query that tells us whether the name is already in People. @max_id captures the current highest id so we can continue the sequence. @current_id is our running counter that increments as we process each distinct name.

     The cursor is defined with SELECT DISTINCT name FROM Predefined_Table FOR READ ONLY. The FOR READ ONLY clause ensures we don’t accidentally lock the source table for updates. If we ever needed to modify rows in the source table from within the cursor, we could switch to FOR UPDATE OF name, but that’s not necessary here.

     After opening the cursor, we perform a quick check to see if People already contains rows. If it does, we grab the maximum id and set @current_id to that value. If the table is empty, @current_id starts at 0. This logic guarantees that the new rows receive unique identifiers that follow the existing sequence.

     The core of the procedure is the FETCH NEXT loop. Each iteration pulls the next distinct name into @name_string. We immediately bump @current_id to reserve a new id for the potential insert. Then we query People to see if that name already exists. If the count returns zero, we perform an INSERT. If the name is a duplicate, we roll back the id increment by subtracting one, so the next iteration will reuse the same id value for the next distinct name.

     Finally, after the loop terminates - when @@FETCH_STATUS is not zero - the cursor is closed and deallocated. This frees the resources that were allocated when we declared the cursor. Proper cleanup is essential; failing to deallocate a cursor can lead to memory leaks or resource exhaustion, especially on busy servers.

     Although the procedure uses a cursor, the overall logic remains straightforward. The cursor gives us the fine‑grained control needed to check for duplicates on a row‑by‑row basis and to generate sequential id values that respect the current state of the table. If you find yourself in a situation where you need to perform similar row‑by‑row processing - be it data migration, complex updates, or audit logging - a cursor can be a useful tool, provided you keep the implementation tidy and clean up after yourself.