How to use cursor in SQL

Description : In this post learn about Cursor. Cursor is difficult concept in SQL, cursor let you create a lop in your SQL procedure you can fatch data record by record. cursor stored data set that go through each record and than prform some kind of logic in record field

- Here is simple example of print state name and its short name

-- First declare State name and short name variables
DECLARE @sta_StateName Varchar(50), @sta_StateShortName Varchar(50)

-- Declare cursor name is state cursor that fatch 5 rows from State Master table
DECLARE StateCursor CURSOR FOR SELECT TOP 5 sta_StateName, sta_StateShortName FROM StateMaster

-- open the cursor
OPEN StateCursor;

-- here fetch 1st row from cursor
FETCH NEXT FROM StateCursor INTO @sta_StateName, @sta_StateShortName

-- while loop
WHILE @@FETCH_STATUS = 0
BEGIN
   
    -- print fetched state name
    PRINT @sta_StateName + ' ['+@sta_StateShortName+']'

    -- again fetch next row from cursor
    FETCH NEXT FROM StateCursor INTO @sta_StateName, @sta_StateShortName
END;

-- close the cursor
CLOSE StateCursor;

-- deallocate cursor
DEALLOCATE StateCursor;