Using cursor in Sql server with example (Iteration-looping)

Processing each record Row By Row - one at a  time is need of most Sql Developer.  Most of us know its using cursor but might forgot syntax.
Here is syntax / example :

PRINT 'DECLARE VARIBLE REQURED DURING ITERATION'

DECLARE @Row_EmployeeName AS VARCHAR(255)

PRINT 'DECALRE CURSOR'

DECLARE MyCursor CURSOR FOR

SELECT TOP 3 Name as EmployeeName

FROM employee

ORDER BY Name

PRINT 'OPEN CURSOR'

OPEN MyCursor

PRINT 'GET NEXT RECORDD - Here it will be first into @Row_EmployeeName'

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

PRINT 'CHECK IF WE HAVE RECORDS- if yes iterate else stop'

IF @@FETCH_STATUS <> 0

PRINT ' <<None>>'

PRINT 'ITERATE EACH ROW';PRINT'==============================================================';

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' PROCESSING CODE- TO DO - FOR EACH RECORD'

PRINT ' @Row_EmployeeName -- ' + @Row_EmployeeName

PRINT ' GET NEXT RECORDD # - into @Row_EmployeeName'

PRINT '==============================================================';

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

END

PRINT 'CLOSE CURSOR'

CLOSE MyCursor;

PRINT 'DEALLOCATE CURSOR'

DEALLOCATE MyCursor;

Explanation step by step :

1. 1. DECALRE CURSOR

Here we assign name (‘MyCursor’) to cursor and then get data for processing. In our example we are going to process each row in employee table, will be using Name as our field to process/key field.

DECLARE MyCursor CURSOR FOR

SELECT TOP 3 Name as EmployeeName

FROM employee

ORDER BY Name

2. 2. DECLARE VARIABLE

We need to declare variable so that during processing each row one by bone we can assign it value of Key field from source

DECLARE @Row_EmployeeName AS VARCHAR(255)

3. 3. OPEN CURSOR AND CLOSE CURSOR

Now our cursor has data. Following is syntax to open and close cursor.

OPEN MyCursor

...

CLOSE MyCursor

4. 4. SET VALUE TO VARIABLE

Following syntax basically set next key value. It will get first record if it’s not been processed.

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

5. 5. PROCESS EACH ROW

Now cursor has all values and between opening and closing cursor we need to write code for processing each row one by one. We already set value of first record using step 4.

Following is syntax to process all row (including first row ):

IF @@FETCH_STATUS <> 0

PRINT ' <<None>>'

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' PROCESSING CODE- TO DO - FOR EACH RECORD'

PRINT ' @Row_EmployeeName -- ' + @Row_EmployeeName

PRINT ' GET NEXT RECORDD # - into @Row_EmployeeName'

PRINT '===============================================';

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

END

Here @@FETCH_STATUS will become 0 if there is no row or all rows have been processed.

That’s why we are using it in while and when one record is processed need to fetch next using

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

6. 6. DEALLOCATE CURSOR

Once we are done with all processing best practice is to deallocate cursor .

Hope this useful !!!