cursor is a database
objects to retrieve data from a result set one row at a time, instead of the
T-SQL commands that operate on all the rows in the result set at one time. We
use cursor when we need to update records in a database table in singleton
fashion means row by row.
Declare Cursor
A cursor is declared by defining the SQL statement that returns
a result set.
Open
A Cursor is opened and populated by executing the SQL statement
defined by the cursor.
Fetch
When cursor is opened, rows can be fetched from the cursor one
by one or in a block to do data manipulation.
Close
After data manipulation, we should close the cursor explicitly.
Deallocate
Finally, we need to delete the cursor definition and released
all the system resources associated with the cursor.
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SQL Server Cursor Components
Based on the example above, cursors include these components:- DECLARE
statements - Declare variables used in the code block
- SET\SELECT
statements - Initialize the variables to a specific value
- DECLARE CURSOR
statement - Populate the cursor with values that will be evaluated
- NOTE - There
are an equal number of variables in the DECLARE <cursor_name>
CURSOR FOR statement as there are in the SELECT statement. This
could be 1 or many variables and associated columns.
- OPEN statement
- Open the cursor to begin data processing
- FETCH NEXT
statements - Assign the specific values from the cursor to the variables
- NOTE - This
logic is used for the initial population before the WHILE statement and
then again during each loop in the process as a portion of the WHILE
statement
- WHILE
statement - Condition to begin and continue data processing
- BEGIN...END
statement - Start and end of the code block
- NOTE - Based
on the data processing multiple BEGIN...END statements can be used
- Data
processing - In this example, this logic is to backup a database to a
specific path and file name, but this could be just about any DML or administrative
logic
- CLOSE
statement - Releases the current data and associated locks, but permits
the cursor to be re-opened
- DEALLOCATE
statement - Destroys the cursor
Syntax to Deallocate Cursor
Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:
DEALLOCATE cursor_name --after deallocation it can't be reopen
SQL SERVER – Simple Examples of Cursors
CREATE TABLE Employee
(
EmpID int PRIMARY KEY,
EmpName varchar (50) NOT NULL,
Salary int NOT NULL,
Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
DECLARE cur_emp CURSOR
STATIC FOR
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
WHILE @@Fetch_status = 0
BEGIN
PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
END
1. END
2. CLOSE cur_emp
3. DEALLOCATE cur_emp
4. SET NOCOUNT OFF
