Wednesday, November 17, 2010

SQL Server Cursor Examples
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
o 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
o 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
o 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

No comments:

Post a Comment