How to avoid cursor in SQL Server stored procedures to loop over records?

How to avoid cursor in SQL Server stored procedures to loop over records?

Yes, I agree, not to use cursor in MSSQL stored procedures. It is bad practice to use cursor in SQL server as cursor takes up memory and create extending locks. As you loop records through cursor, you are adding result rows into memory and locking them and as you go through rows, you might making changes to other tables and still keeping all of the memory and locks of the cursor open. Cursor can cause serious performance issue and it affects other users working on same server. Below alternate method, you can use to avoid cursor.

USE TEMP TABLE WITH IDENTITY COLUMN

CREATE PROCEDURE [dbo].[cursorAlternate]
AS
BEGIN
         SET NOCOUNT ON;

         --declare variables here
         DECLARE
                 @i INT, -- iterator
                 @rowCount INT, –row counter
                 @customerName VARCHAR(50) –looping value

         --create temp table to store result set to loop through
         CREATE TABLE #loopTable (ID INT IDENTITY(1,1), CUSTOMER_NAME VARCHAR(50))

         --insert result set to tamp table instead of cursor
         INSERT INTO #loopTable (CUSTOMER_NAME)
         SELECT customer_name
         FROM CUSTOMER

         --count number of rows
         SET @rowCount = @@ROWCOUNT --SCOPE_IDENTITY() would also work

         /* Its better to add index in at one time after insert. It is always faster than to update the index as you write into the temp table. */
         CREATE CLUSTERED INDEX idx_tmp on #loopTable (ID) WITH FILLFACTOR = 100

         --LOOP UNTIL RECORDS ARE AVAILABLE.
         WHILE @i<=@rowCount
         BEGIN
                 SELECT @customerName = CUSTOMER_NAME
                 FROM #loopTable
                 WHERE ID = @id

                 -- Begin Transactions
                 -- Enter all your operations/Transactions here
                 PRINT @customerName
                 -- Commit Transactions

                 SET @i = @i + 1 – incremental

         END

         DROP TABLE#loopTable

END

Jaymin Soni

I recommend using explicit transactions if you are performing INSERT/UPDATE/DELETE.

Leave a Comment