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
I recommend using explicit transactions if you are performing INSERT/UPDATE/DELETE.