Cursors in SQL procedures


Welcome To All
In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.
A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
To use cursors in SQL procedures, you need to do the following:
  1. Declare a cursor that defines a result set.
  2. Open the cursor to establish the result set.
  3. Fetch the data into local variables as needed from the cursor, one row at a time.
  4. Close the cursor when done
To work with cursors you must use the following SQL statements:
  • DECLARE CURSOR
  • OPEN
  • FETCH
  • CLOSE
The following example demonstrates the basic use of a read-only cursor within an SQL procedure: 
CREATE PROCEDURE sum_salaries(OUT sum INTEGER) 
  LANGUAGE SQL
  BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE p_sum INTEGER;
    DECLARE p_sal INTEGER;
    DECLARE c CURSOR FOR SELECT SALARY FROM EMPLOYEE;
 
     SET p_sum = 0;

     OPEN c;

     FETCH FROM c INTO p_sal;

     WHILE(SQLSTATE = '00000') DO
        SET p_sum = p_sum + p_sal;
        FETCH FROM c INTO p_sal; 
     END WHILE;

     CLOSE c;

     SET sum = p_sum;

  END%
Here is a more complex example of use of a cursor within an SQL procedure. This example demonstrates the combined use of a cursor and SQL PL statements.


............................................................................................................................................................


General concepts

In this article, I want to tell you how to create and use server side cursors and how you can optimize a cursor performance.
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable. The server side cursors were first added in the SQL Server 6.0 release and are now supported in all editions of SQL Server 7.0 and SQL Server 2000.
Before using cursor, you first must declare the cursor. Once a cursor has been declared, you can open it and fetch from it. You can fetch row by row and make multiple operations on the currently active row in the cursor. When you have finished working with a cursor, you should close cursor and deallocate it to release SQL Server resources.


Declaring a Cursor

Before using cursor, you first must declare the cursor, i.e. define its scrolling behavior and the query used to build the result set on which the cursor operates. To declare cursor, you can use a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.


SQL-92 Syntax

This is SQL-92 Syntax: 

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]
where
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
INSENSITIVE - Specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor.
SCROLL - Specifies that cursor can fetch data in all directions, not only sequentially until the end of the result set. If this argument is not specified, FETCH NEXT is the only fetch option supported.
select_statement - The standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.
READ ONLY - Specifies that cursor cannot be updated.
UPDATE [OF column_name [,...n]] - Specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Cursor Options Compatibility

INSENSITIVE SCROLL READ ONLY UPDATE
INSENSITIVE Yes Yes No
SCROLL Yes Yes Yes
READ ONLY Yes Yes No
UPDATE No Yes No


Transact-SQL Extended Syntax

This is Transact-SQL Extended Syntax: 

DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]
where
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
LOCAL - Specifies that cursor can be available only in the batch, stored procedure, or trigger in which the cursor was created. The LOCAL cursor will be implicitly deallocated when the batch, stored procedure, or trigger terminates.
GLOBAL - Specifies that cursor is global to the connection. The GLOBAL cursor will be implicitly deallocated at disconnect.
FORWARD_ONLY - Specifies that cursor can only fetch data sequentially from the first to the last row. FETCH NEXT is the only fetch option supported.
STATIC - Specifies that cursor will use a temporary copy of the data instead of base tables. This cursor does not allow modifications and modifications made to base tables are not reflected in the data returned by fetches made to this cursor.
KEYSET - Specifies that cursor uses the set of keys that uniquely identify the cursor's rows (keyset), so that the membership and order of rows in the cursor are fixed when the cursor is opened. SQL Server uses a table in tempdb to store keyset. The KEYSET cursor allows updates nonkey values from being made through this cursor, but inserts made by other users are not visible. Updates nonkey values made by other users are visible as the owner scrolls around the cursor, but updates key values made by other users are not visible. If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2.
DYNAMIC - Specifies that cursor reflects all data changes made to the base tables as you scroll around the cursor. FETCH ABSOLUTE option is not supported with DYNAMIC cursor.
FAST_FORWARD - Specifies that cursor will be FORWARD_ONLY and READ_ONLY cursor. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server.
READ ONLY - Specifies that cursor cannot be updated.
SCROLL_LOCKS - Specifies that cursor will lock the rows as they are read into the cursor to ensure that positioned updates or deletes made through the cursor will be succeed.
OPTIMISTIC - Specifies that cursor does not lock rows as they are read into the cursor. So, the positioned updates or deletes made through the cursor will not succeed if the row has been updated outside the cursor since this row was read into the cursor.
TYPE_WARNING - Specifies that if the cursor will be implicitly converted from the requested type to another, a warning message will be sent to the client.
select_statement - The standard select statement, cannot contain COMPUTE, COMPUTE BY, FOR BROWSE, and INTO keywords.
UPDATE [OF column_name [,...n]] - Specifies that all cursor's columns can be updated (if OF column_name [,...n] is not specified), or only the columns listed in the OF column_name [,...n] list allow modifications.

Cursor Options Compatibility

LOCAL GLOBAL FORWARD
ONLY 		STATIC KEYSET DYNAMIC FAST
FORWARD 		READ
ONLY 		SCROLL
LOCKS 		OPTIMISTIC TYPE
WARNING 		UPDATE
LOCAL No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
GLOBAL No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
FORWARD_ONLY Yes Yes Yes Yes Yes No Yes Yes Yes Yes Yes
STATIC Yes Yes Yes No No No Yes No Yes Yes No
KEYSET Yes Yes Yes No No No Yes Yes Yes Yes Yes
DYNAMIC Yes Yes Yes No No No Yes Yes Yes Yes Yes
FAST_FORWARD Yes Yes No No No No Yes No No Yes No
READ_ONLY Yes Yes Yes Yes Yes Yes Yes No No Yes No
SCROLL_LOCKS Yes Yes Yes No Yes Yes No No No Yes Yes
OPTIMISTIC Yes Yes Yes Yes Yes Yes No No No Yes Yes
TYPE_WARNING Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
UPDATE Yes Yes Yes No Yes Yes No No Yes Yes Yes


Opening a Cursor

Once a cursor has been declared, you must open it to fetch data from it. To open a cursor, you can use the following syntax: 

OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
where
GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be opened; otherwise, the global cursor will be opened.
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - The name of a cursor variable that references a cursor.
After a cursor is opening, you can determine the number of rows that were found by the cursor. To get this number, you can use @@CURSOR_ROWS scalar function.


Fetching a Cursor

Once a cursor has been opened, you can fetch from it row by row and make multiple operations on the currently active row in the cursor. To fetch from a cursor, you can use the following syntax: 

FETCH
        [    [    NEXT | PRIOR | FIRST | LAST
                | ABSOLUTE {n | @nvar}
                | RELATIVE {n | @nvar}
            ]
            FROM
        ]
{ { [GLOBAL] cursor_name } | @cursor_variable_name}
[INTO @variable_name[,...n] ]
where
NEXT - The default cursor fetch option. FETCH NEXT returns the next row after the current row.
PRIOR - Returns the prior row before the current row.
FIRST - Returns the first row in the cursor.
LAST - Returns the last row in the cursor.
ABSOLUTE {n \| @nvar} - Returns the nth row in the cursor. If a positive number was specified, the rows are counted from the top of the data set; if 0 was specified, no rows are returned; if a negative number was specified, the number of rows will be counted from the bottom of the data set.
RELATIVE {n \| @nvar} - Returns the nth row in the cursor relative to the current row. If a positive number was specified, returns the nth row beyond the current row; if a negative number was specified, returns the nth row prior the current row; if 0 was specified, returns the current row.
GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be fetched; otherwise, the global cursor will be fetched.
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - The name of a cursor variable that references a cursor.
INTO @variable_name[,...n] - Allows data returned from the cursor to be held in temporary variables. The type of variables must match the type of columns in the cursor select list or support implicit conversion. The number of variables must match the number of columns in the cursor select list.


Closing a Cursor

When you have finished working with a cursor, you can close it to release any resources and locks that SQL Server may have used while the cursor was open.
To close a cursor, you can use the following syntax: 

CLOSE { { [GLOBAL] cursor_name } | cursor_variable_name }
where
GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be closed; otherwise, the global cursor will be closed.
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - The name of a cursor variable that references a cursor.
Note. If you have closed a cursor, but have not deallocated it, you can open it again when needed.


Deallocating a Cursor

When you have finished working with a cursor and want to completely release SQL Server resources that were used by a cursor, you can deallocate a cursor.
To deallocate a cursor, you can use the following syntax: 

DEALLOCATE { { [GLOBAL] cursor_name } | @cursor_variable_name}
where
GLOBAL - If this argument was not specified and both a global and a local cursor exist with the same name, the local cursor will be deallocated; otherwise, the global cursor will be deallocated.
cursor_name - The name of the server side cursor, must contain from 1 to 128 characters.
cursor_variable_name - The name of a cursor variable that references a cursor.
Note. Deallocating a cursor completely removes all cursor references. So, after a cursor is deallocated, it no longer can be opened.


.................................................................................................................................................................


ProblemIn my T-SQL code I always use set based operations.  I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing.  I know cursors exist but I am not sure how to use them.  Can you provide some cursor examples?  Can you give any guidance on when to use cursors?  I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.
SolutionIn some circles cursors are never used, in others they are a last resort and in other groups they are used regularly.  In each of these camps they have different reasons for their stand on cursor usage.  Regardless of your stand on cursors they probably have a place in particular circumstances and not in others.  So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not.  To get started let's do the following:
  • Look at an example cursor
  • Break down the components of the cursor
  • Provide additional cursor examples
  • Analyze the pros and cons of cursor usage
Example 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
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
Additional Cursor Examples
In the example above backups are issued via a cursor, check out these other tips that leverage cursor based logic:
Cursor Analysis
The analysis below is intended to serve as insight into various scenarios where cursor based logic may or may not be beneficial:
  • Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions.  Our team has run into a third party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence.  Typically, SET based logic is more than feasible and cursors are rarely needed.
  • Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed.  However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values.  We have had the same experience when needing to aggregate data for downstream processes, a cursor based approach was quick to develop and performed in an acceptable manner to meet the need.
  • Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
  • Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need.  In some of those circumstances, cursors are used to complete the process.
  • Large data sets - With large data sets you could run into any one or more of the following:
    • Cursor based logic may not scale to meet the processing needs.
    • With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues.  As such, a cursor based approach may meet the need.
    • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
    • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed.  All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
    • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
    • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor.  However, with a set based approach that may not be the case until an entire set of data is completed.  As such, troubleshooting the row with the problem may be more difficult.
Cursor Alternatives
Below outlines alternatives to cursor based logic which could meet the same needs:
Next Steps
  • When you are faced with a data processing decision determine where you stand with SQL Server cursor usage.  They may or may not have a place in your application or operational processes.  There are many ways to complete a task, so using a cursor could be a reasonable alternative or not.  You be the judge.
  • If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative.  It may take longer to process the data, but the coding time might be much less.  If you have a one time process or nightly processing, this could do the trick.
  • If cursors are shunned in your environment, be sure to select another viable alternative.  Just be sure the process will not cause other issues.  As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention?  Or with a large data set will the data be paged to disk or written to a temporary directory?
  • As you evaluate a cursor based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed.  Hopefully these factors will drive you to the proper technique.

...........................................................................................................................................................................



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Font Size
In a relational dtaabse, operations are carried out on a complete set of rows in a table. The SELECT statement returns all the rows that satisfy the condition in the WHERE clause of the statement. This complete set of rows that the SELECT statement returns is called a resultset.
Some applications, such as interactive and online applications, cannot work effectively with the entire set as a unit. These applications need a mechanism to work with one row, or a small block of rows at a time.
For example, consider that you want to see a list of soaps available in a shop. In technical terms, the user wants selected records from the table. Once these records are available, you also would like to see all the records one by one.
A cursor is a database object that applications use to manipulate data by rows instead of recordsets. You can use cursors to perform multiple operations in a row-by-row manner, against the resultset. You can do this with or without returning to the original table. In other words, cursors conceptually return a resultset based on tables within a database.
You can use the cursors to do the following:
  • Allow positioning at specific rows of the resultset.
  • Retrieve a single row, or set of rows, from the current positions in the resultset.
  • Support data modifications to the rows at the current position in the resultset.
  • Support different levels of visibility to changes that others users make to the database data that is presented in the resultset.
  • Provide T-SQL statements in scripts, stored procedures, and triggers, to access the data in the resultset.

Creating a Cursor

You can use the following two methods to create a cursor in SQL Server:
  1. The T-SQL language, which supports the syntax for using cursors modelled after the Sql-92 cursor syntax.
  2. Database application programming interface.
In this article, we will focus on T-SQl cursors. The syntax of T-SQL cursors and API cursors is different, but they follow a common sequence of steps.
Follow these steps to create a cursor:
  1. Associate a cursor with a resultSet of a T-SQL statement, and define the characteristics of the cursor, such as how the rows are going to be retrieved, and so forth.
  2. Execute the T-SQL statement to populate the cursor.
  3. Retrieve the rows in the cursor. The operation to retrieve one row or a set of rows is called fetch. Scrolling is a series of fetch operations to retrieve the rows in a backward or forward direction.
  4. You can also perform the modifications on a row at the cursor position.
  5. Close the cursor.

Now We Shall See the Syntax

  1. Use the DECLARE statement to create the cursor. It contains a SQL statement to include the records from the table.
    2. DECLARE <Cursor_Name> CURSOR
FOR
<Select Statement>
  2. After creating the cursor, you will open it. Use the OPEN statement to make the cursor accessible.
    3. OPEN <Cursor_name>
  3. Use the FETCH statement to obtain the records from the cursor for further processing.
    4. FETCH <Cursor_name>
  4. Use the CLOSE statement to temporarily close the cursor when it is not required. This statement releases the current resultset to close an open cursor. You have to re-open the cursor to fetch the rows.
    5. CLOSE <Cursor_Name>
  5. When you no longer require the cursor, you can use the DEALLOCATE statement to remove its reference.
    6. DEALLOCATE <Cursor_Name>

Fetching and Scrolling Through a Cursor

When you open a cursor, the current row pointer in the cursor is logically before the first row. T-SQL cursors can fetch one row at a time. The options for FETCH operations are as follows:
Option Description
FETCH FIRST Fetches the first row in the cursor
FETCH NEXT Fetches the row after the previously fetched row
FETCH PRIOR Fetches the row before the previously fetched row
FETCH LAST Fetches the last row in the cursor
FETCH ABSOLUTE n If n is a positive integer, it fetches the nth row in a cursor. If n is a negative integer, it fetches the nth row before the last row. If n is 0, no row is fetched.
FETCH RELATIVE n If n is positive, it fetches the nth row from the previously fetched row. If n is negative, it fetches the nth row before the previously fetched row.If n is 0, the same row is fetched again.
By default, the FETCH NEXT option works. To use other options, you must include certain options in the DECLARE statement while creating the cursors.

DECLARE Statement Options

You can add the following attributes to the cursor to enhance its scrollability of a cursor. The attributes also are explained in the following list.
DECLARE <Cursor_Name> CURSOR
[LOCAL | GLOBAL]
[FORWARD ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR <Selecting Statements>
[FOR UPDATE [OF Column_name[,....N]]]
  • LOCAL: Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. An OUTPUT parameter is used to pass the local cursor back to the calling batch, stored procedure, or trigger, which can assign the parameter to a cursor variable to reference the cursor after the stored procedure terminates. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless the cursor was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.
  • GLOBAL: Specifies that the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is only implicitly deallocated at disconnect.
  • FORWARD_ONLY: Specifies that the cursor can only be scrolled from the first to the last row. FETCH NEXT is the only supported fetch option. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor operates as a DYNAMIC cursor. When neither FORWARD_ONLY nor SCROLL is specified, FORWARD_ONLY is the default, unless the keywords STATIC, KEYSET, or DYNAMIC are specified. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY is supported with STATIC, KEYSET, and DYNAMIC Transact-SQL cursors. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.
  • STATIC: Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.
  • KEYSET: Specifies that the membership and order of rows in the cursor are fixed when the cursor is opened. The set of keys that uniquely identify the rows is built into a table in tempdb known as the keyset. Changes to nonkey values in the base tables, either made by the cursor owner or committed by other users, are visible as the owner scrolls around the cursor. Inserts made by other users are not visible (inserts cannot be made through a Transact-SQL server cursor). If a row is deleted, an attempt to fetch the row returns an @@FETCH_STATUS of -2. Updates of key values from outside the cursor resemble a delete of the old row followed by an insert of the new row. The row with the new values is not visible, and attempts to fetch the row with the old values return an @@FETCH_STATUS of -2. The new values are visible if the update is done through the cursor by specifying the WHERE CURRENT OF clause.
  • DYNAMIC: Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.
  • FAST_FORWARD: Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified. FAST_FORWARD and FORWARD_ONLY are mutually exclusive; if one is specified, the other cannot be specified.
  • READ_ONLY: Prevents updates from being made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.
  • SCROLL_LOCKS: Specifies that positioned updates or deletes made through the cursor are guaranteed to succeed. Microsoft SQL Server locks the rows as they are read into the cursor to ensure their availability for later modifications. SCROLL_LOCKS cannot be specified if FAST_FORWARD is also specified.
  • OPTIMISTIC: Specifies that positioned updates or deletes made through the cursor do not succeed if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. It instead uses comparisons of timestamp column values, or a checksum value if the table has no timestamp column, to determine whether the row was modified after it was read into the cursor. If the row was modified, the attempted positioned update or delete fails. OPTIMISTIC cannot be specified if FAST_FORWARD is also specified.
  • TYPE_WARNING: Specifies that a warning message is sent to the client if the cursor is implicitly converted from the requested type to another. select_statement is a standard SELECT statement that defines the result set of the cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO are not allowed within a select_statement of a cursor declaration. SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type. For more information, see Implicit Cursor Conversions.
    UPDATE [OF column_name [,...n]] defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

Example 1

Declare Flight_cursor CURSOR SCROLL
FOR SELECT * from flight Order By Aircraft_code
Open Flight_Cursor
Fetch First From Flight_Cursor
While @@Fetch_Status =0
BEGIN
Fetch NExt from Flight_Cursor
End

Explanation of Example 1

This simply creates a cursor that selects all records from a flight table, then it opens the cursor. Then, it fetches the first record from the cursor and it moves the cursor ahead until the last executed cursor FETCH statement returns 0.
@@FETCH_STATUS returns an integer for the last executed cursor FETCH statement. @@CURSOR_ROWS returns the number of qualifying rows that are present in the currently open cursor.

Example 2

Declare @@counter int
set @@counter=0
Declare @@ProductID int
Declare @@ProductName varchar(30)
Declare @@Qty int

Declare special cursor
local Scroll Keyset Optimistic
For
Select * from Products
Open special    /* Opening the cursor */
fetch Absolute 1 from special
into @@ProductID,@@ProductName,@@Qty

while @@fetch_Status<>-1
begin
fetch next from special
into @@ProductID,@@ProductName,@@Qty
Print @@ProductiD
Print @@ProductName
print @@Qty

/*set @@ProductName=(Select Prod_Name from special)*/
Update Products set Prod_Name= @@counter

set @@counter=@@counter+1
end
close special
Deallocate special

Explanation of Example 2

In this cursor, we are fetching the values from the recordSet in set of variables. We further use them to update the record. CLOSING and DEALLOCATING is a good practice; it increase performance and security.

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)