Tuesday 16 July 2013

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…………….
  1. Open cursor
OPEN ins_cursor
  1. Fetch row from the cursor
FETCH NEXT FROM ins_cursor 
INTO @fileno,……….variable list
  1. Process fetched row
WHILE @@FETCH_STATUS = 0
BEGIN
….
…..
FETCH NEXT FROM ins_cursor 
INTO @fileno@fileno,……….variable list
END
  1. Close cursor
CLOSE ins_cursor
  1. 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 2nd 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


1 comment:

  1. Good explainstion using simple example.
    Thanks

    ReplyDelete

Please leave a comment for this post