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:
- Declare
cursor
DECLARE ins_cursor CURSOR
FOR
Select
statement…………….
- Open
cursor
OPEN ins_cursor
- Fetch
row from the cursor
FETCH NEXT FROM ins_cursor
INTO @fileno,……….variable list
- Process
fetched row
WHILE @@FETCH_STATUS = 0
BEGIN
….
…..
…
FETCH NEXT FROM ins_cursor
INTO @fileno@fileno,……….variable list
END
- Close
cursor
CLOSE ins_cursor
- 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
