top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to create cursor in sql server 2008?

+3 votes
104 views
How to create cursor in sql server 2008?
posted Jun 2, 2015 by Shivaranjini

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer

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.

Example SQL Server Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

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

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 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

answer Jun 3, 2015 by Manikandan J
...