Where to use SQL Server Cursor

Where to Use Cursor

Cursor can be used when you need to manipulate data in a set on a row-by-row basis, however you can also use T-SQL WHILE loop, CASE expression and some system defined stored procedure like sp_MSforeachdb, sp_MSforeachdb etc.

IN SQL Server the cursor can be implemented by 6 step process as:

1. Declare cursor

DECLARE ins_cursor CURSOR

FOR

    Select statement…………….

2. Open cursor

OPEN ins_cursor

3. Fetch row from the cursor

FETCH NEXT FROM ins_cursor 

INTO @fileno,……….variable list

4. Process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

….

…..

…

FETCH NEXT FROM ins_cursor 

INTO @fileno@fileno,……….variable list

END

5. Close cursor

CLOSE ins_cursor

6. Deallocate cursor

DEALLOCATE ins_cursor;

I have used cursor first time when I need to insert data from one table of one database into tables of another database. So I am explaining same example here

--declare variables to use in logic

DECLARE @RID INT

DECLARE @FILENO VARCHAR(50),@ISSUEDATE DATETIME,@REQDATE DATETIME,@RCN VARCHAR(50),@FILEREMARKS VARCHAR(200)

--decalare cusrsor

DECLARE INS_CURSOR CURSOR

    FOR

    --select statement

    SELECT FILENO,REQCONTROLNO,CONVERT(DATETIME,REPLACE(REQDATE,'-','/'),103),CONVERT(DATETIME,REPLACE(ISSUEDATE,'-','/'),103) ,REMARKS FROM ISSUE WHERE RECEIVEDDATE IS NULL

    --open cursor

OPEN INS_CURSOR

--Fetch row from the cursor

FETCH NEXT FROM INS_CURSOR

INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

--process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

--insert into first table

INSERT INTO FISS1.DBO.FILEREQUEST(REQDATE,REQSTATUS,RCN,CORDSTATUS,FILEREMARKS)

VALUES (@REQDATE,1,@RCN,1,@FILEREMARKS)

----assigning primary key(identity) value

SELECT @RID=@@IDENTITY

--insert into 2^nd table with foreign key @rid

INSERT INTO FISS1.DBO.ISSUE(REQID,FILENO,PRIORITY,PURPOSE,ISSUEDATE,STAUS)

VALUES(@RID,@FILENO,1,'GENERAL',@ISSUEDATE,1)

FETCH NEXT FROM INS_CURSOR INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

END

--close cursor

CLOSE INS_CURSOR;

DEALLOCATE INS_CURSOR;

Cursor Recommendations:

·         It is better to avoid using cursor because they consume memory for execution, so performance is less.

·         If you using cursor, then you should always close cursor after using it.

Further Reading

http://msdn.microsoft.com/en-us/library/ms181441.aspx

Prev---> Handling Null Values in SQL Server