1. 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 ] ] ]
[;]
2. Where can it be used?
1)
Ad
Hoc Batches
2)
SPs
3)
Triggers
3. When should it be used? – A short answer: you probably should not use it unless you are a database admin
1)
Some
administrative tasks such as rebuild each index by looping through the cursor
2)
You
need to perform different operation on different rows in a table based on
criteria that are more complex than can be stated in a WHERE clause.
Most of the cursors can be re-written to be as a set-based operation. See below for examples.
http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them
4. What is the basic procedure to use a cursor?
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
5) Deallocate the
cursor to release the resource
5. De-mystifying the options for a cursor with examples
1) LOCAL vs. GLOBAL: specify the scope of the cursor. Default to GLOBAL (a DB option).
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 only valid 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.
If neither GLOBAL nor LOCAL is specified, the default is
controlled by the setting of the default to local cursor database
option, which is GLOBAL by default.
2) FORWARD_ONLY vs. SCROLL: specify the direction of the cursor. Default to Forward_Only or Fetch Next
FORWARD_ONLY – from the 1st to the
last row (but it is NOT read_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.
Forward
Only Cursor – Example
/*First, prepare the table data. The example is from the link below. The
same table is also used for other examples of cursor unless specified.
Examples:
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
-- Note: If FORWARD_ONLY is specified without the STATIC,
KEYSET, or DYNAMIC keywords, the cursor
-- operates as a DYNAMIC cursor.
--Forward Only Cursor for Update
SET NOCOUNT ON
DECLARE @Id INT
DECLARE @name VARCHAR(50)
DECLARE Forward_cur_empupdate CURSOR FORWARD_ONLY
FOR
SELECT EmpID ,
EmpName
FROM Employee
ORDER BY EmpName
OPEN Forward_cur_empupdate
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Forward_cur_empupdate INTO @Id, @name
WHILE @@Fetch_status = 0
BEGIN
IF @name = 'Amit'
UPDATE
Employee
SET Salary = 24000
WHERE CURRENT OF Forward_cur_empupdate
FETCH NEXT FROM Forward_cur_empupdate INTO @Id, @name
END
END
CLOSE Forward_cur_empupdate
DEALLOCATE Forward_cur_empupdate
SET NOCOUNT OFF
Go
SELECT *
FROM
Employee
-- Forward Only Cursor for Delete
SET NOCOUNT ON
DECLARE @Id INT
DECLARE @name VARCHAR(50)
DECLARE Forward_cur_empdelete CURSOR FORWARD_ONLY
FOR
SELECT EmpID ,
EmpName
FROM Employee
ORDER BY EmpName
OPEN Forward_cur_empdelete
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Forward_cur_empdelete INTO @Id, @name
WHILE @@Fetch_status = 0
BEGIN
IF @name = 'Sonu'
DELETE
Employee
WHERE CURRENT OF Forward_cur_empdelete
FETCH NEXT FROM Forward_cur_empdelete INTO @Id, @name
END
END
CLOSE Forward_cur_empdelete
DEALLOCATE Forward_cur_empdelete
SET NOCOUNT OFF
Go
SELECT *
FROM
Employee
SCROLL – starting from anywhere, relative or
absolute
Specifies that all fetch options (FIRST, LAST, PRIOR, NEXT,
RELATIVE, ABSOLUTE) are available. If SCROLL is not specified in an ISO DECLARE
CURSOR, NEXT is the only fetch option supported. SCROLL cannot be specified if
FAST_FORWARD is also specified.
The target position for a scrollable cursor can be
specified relatively (from the current cursor position) or absolutely (from the
beginning of the result set).
FETCH [NEXT|PRIOR|FIRST|LAST] FROM cursor_name
FETCH ABSOLUTE n FROM cursor_name
FETCH RELATIVE n FROM cursor_name
3) STATIC/ FAST_FORWARD /KEYSET/DYNAMIC: specify the type of a cursor in terms of the availability of the changes in the underlying table visible to the cursor. Default to FAST_FORWARD
STATIC – When the cursor is opened, it
makes a temporary copy of the data to be used by the cursor. Thus, it is a
read-only cursor, supporting both forward and backward reads on the temp table.
The underlying table can be modified but not reflected in the cursor table.
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.
The complete result set of a static cursor is built in tempdb when
the cursor is opened. A static cursor always displays the result set as it was
when the cursor was opened. Static cursors detect few or no changes, but
consume relatively few resources while scrolling.
The cursor does not reflect any changes made in the database
that affect either the membership of the result set or changes to the values in
the columns of the rows that make up the result set. A static cursor does not
display new rows inserted in the database after the cursor was opened, even if
they match the search conditions of the cursor SELECT statement. If rows making
up the result set are updated by other users, the new data values are not
displayed in the static cursor. The static cursor displays rows deleted from
the database after the cursor was opened. No UPDATE, INSERT, or DELETE
operations are reflected in a static cursor (unless the cursor is closed and
reopened), not even modifications made using the same connection that opened
the cursor.
SQL Server static cursors are always read-only.
Because the result set of a static cursor is stored in a work
table in tempdb, the size of the rows in the result set cannot
exceed the maximum row size for a SQL Server table.
Transact-SQL uses the term insensitive for static cursors. Some
database APIs identify them as snapshot cursors. (from http://msdn.microsoft.com/en-us/library/ms191179.aspx)
==From Grant Fritchey’s Book SQL SERVER 2012 QUERY PERFORMANCE TUNING ==
These are the
characteristics of static cursors:
·
They create a
snapshot of cursor results in the tempdb database when the cursor is opened.
Thereafter, static cursors operate on the snapshot in the tempdb database.
·
Data is retrieved
from the underlying table(s) when the cursor is opened.
·
Static cursors
support all scrolling options: FETCH FIRST, FETCH NEXT, FETCH
PRIOR, FETCH LAST, FETCH ABSOLUTE n, and FETCH RELATIVE n.
·
Static cursors
are always read-only; data modifications are not allowed through static
cursors. Also, changes (INSERT, UPDATE, and DELETE) made to the
underlying table(s) are not reflected in the cursor.
·
Some tests show
that a static cursor can perform as well as—and sometimes faster than—a
forward-only cursor. Be sure to test this behavior on your own system.
--Static
Cursor – Example using the above table data
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
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF
FAST_FORWARD – forward ready only. But the rows are
not retrieved until they are fetched. Thus, the changes made to the underlying
table are visible to the fetch.
Specifies
a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled (What
does it mean? How is a FAST_FORWARD cursor different from a FORWARD_ONLY,
READ_ONLY cursor? See http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx for
details)
FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also
specified.
Note: In SQL Server 2005 and later, both FAST_FORWARD and
FORWARD_ONLY can be used in the same DECLARE CURSOR statement.
A forward-only cursor does not support scrolling; it supports
only fetching the rows serially from the start to the end of the cursor. The
rows are not retrieved from the database until they are fetched. The effects of
all INSERT, UPDATE, and DELETE statements made by the current user or committed
by other users that affect rows in the result set are visible as the rows are
fetched from the cursor.
Because the cursor cannot be scrolled backward, most changes
made to rows in the database after the row was fetched are not visible through
the cursor. In cases where a value used to determine the location of the row
within the result set is modified, such as updating a column covered by a
clustered index, the modified value is visible through the cursor (http://msdn.microsoft.com/en-us/library/ms191179.aspx)
==from Grant Fritchey’s
Book==
These are the
characteristics of forward-only (aka fast_forward) cursors:
·
They operate
directly on the base table(s).
·
Rows from the
underlying table(s) are usually not retrieved until the cursor rows are fetched
using the cursor FETCH operation. However, the database API
forward-only cursor type, with the following additional characteristics,
retrieves all the rows from the underlying table first:
·
Client-side
cursor location
·
Server-side
cursor location and read-only cursor concurrency
·
They support
forward scrolling only (FETCH NEXT) through the cursor.
·
They allow all
changes (INSERT, UPDATE, and DELETE) through the cursor. Also, these
cursors reflect all changes made to the underlying table(s).
·
A forward-only
cursor with a read-only property can be created using a fast
forward statement. The T-SQL syntax provides a specific cursor type
option, FAST_FORWARD, to create a fast-forward-only cursor. The nickname
for the FAST_FORWARD cursor is the fire hose because
it is the fastest way to move data through a cursor and because all the
information flows one way. However, don’t be surprised when the “firehose” is
still not as fast as traditional set-based operations.
--A code
snippet from Grant’s bool
DECLARE MyCursor CURSOR FAST_FORWARD
FOR
SELECT adt.Name
FROM Person.AddressType AS adt
WHERE adt.AddressTypeID = 1 ;
KEYSET - stores only the primary keys in the background temporary
table and in every row-fetch it queries the original data based on the current
key. It maintains a fixed order of rows in the tempdb database.
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.
Note: If the query references at least one table without
a unique index, the keyset cursor is converted to a static cursor.
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.
In other words, a keyset driven cursor is controlled by a set of unique
identifiers as the keys in the keyset. The keyset depends on all the rows that
qualified the SELECT statement at the time of cursor was opened. A keyset
driven cursor is sensitive to any changes to the data source and
supports update, delete operations. By default keyset driven cursors are
scrollable (http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx)
==from Grant Fritchey’s
Book==
These are the
characteristics of keyset-driven cursors:
·
Keyset cursors
are controlled by a set of unique identifiers (or keys) known as a keyset.
·
The keyset is
built from a set of columns that uniquely identify the rows in the result set.
·
These cursors
create the keyset of rows in the tempdb database when the cursor is
opened.
·
Membership of
rows in the cursor is limited to the keyset of rows created in
the tempdb database when the cursor is opened.
·
On fetching a
cursor row, the database engine first looks at the keyset of rows
in tempdb, and then navigates to the corresponding data row in the
underlying table(s) to retrieve the remaining columns.
·
They support all
scrolling options.
·
Keyset cursors
allow all changes through the cursor.
An INSERT performed
outside the cursor is not reflected in the cursor, since the membership of rows
in the cursor is limited to the keyset of rows created in the tempdb database
on opening the cursor. An INSERT through the cursor appears at the
end of the cursor. A DELETE performed on the underlying table(s)
raises an error when the cursor navigation reaches the deleted row.
An UPDATE on the nonkeyset columns of the underlying table(s) is
reflected in the cursor.
An UPDATE on
the keyset column(s) is treated like a DELETE of an old key value and
the INSERT of a new key value. If a change disqualifies a row for
membership or affects the order of a row, then the row does not disappear or
move unless the cursor is closed and reopened.
Keyset Driven Cursor - Example
-- Keyset driven Cursor for Update
SET NOCOUNT ON
DECLARE @Id INT
DECLARE @name VARCHAR(50)
DECLARE Keyset_cur_empupdate CURSOR KEYSET
FOR
SELECT EmpID ,
EmpName
FROM Employee
ORDER BY EmpName
OPEN Keyset_cur_empupdate
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Keyset_cur_empupdate INTO @Id, @name
WHILE @@Fetch_status = 0
BEGIN
IF @name = 'Pavan'
UPDATE Employee
SET Salary = 27000
WHERE CURRENT OF Keyset_cur_empupdate
FETCH NEXT FROM Keyset_cur_empupdate INTO @Id, @name
END
END
CLOSE Keyset_cur_empupdate
DEALLOCATE Keyset_cur_empupdate
SET NOCOUNT OFF
Go
SELECT *
FROM Employee
-- Keyse Driven Cursor for Delete
SET NOCOUNT ON
DECLARE @Id INT
DECLARE @name VARCHAR(50)
DECLARE Keyset_cur_empdelete CURSOR KEYSET
FOR
SELECT EmpID ,
EmpName
FROM Employee
ORDER BY EmpName
OPEN Keyset_cur_empdelete
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Keyset_cur_empdelete INTO @Id, @name
WHILE @@Fetch_status = 0
BEGIN
IF @name = 'Amit'
DELETE Employee
WHERE CURRENT OF Keyset_cur_empdelete
FETCH NEXT FROM Keyset_cur_empdelete INTO @Id, @name
END
END
CLOSE Keyset_cur_empdelete
DEALLOCATE Keyset_cur_empdelete
SET NOCOUNT OFF
Go
SELECT *
FROM Employee
DYNAMIC – all changes made to the underlying
table is reflected in the fetch result of the cursor
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.
Dynamic cursors are the opposite of static cursors. Dynamic cursors
reflect all changes made to the rows in their result set when scrolling through
the cursor. The data values, order, and membership of the rows in the result
set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by
all users are visible through the cursor. Updates are visible immediately if
they are made through the cursor using either an API function such as SQLSetPos or
the Transact-SQL WHERE CURRENT OF clause. Updates made outside the cursor are
not visible until they are committed, unless the cursor transaction isolation
level is set to read uncommitted.
==From Grant Fritchey’s
Book==
These are the
characteristics of dynamic cursors:
·
Dynamic cursors
operate directly on the base table(s).
·
The membership of
rows in the cursor is not fixed, since they operate directly on the base
table(s).
·
As with
forward-only cursors, rows from the underlying table(s) are not retrieved until
the cursor rows are fetched using a cursor FETCH operation.
·
Dynamic cursors
support all scrolling options except FETCH ABSOLUTE n, since the
membership of rows in the cursor is not fixed.
·
These cursors
allow all changes through the cursor. Also, all changes made to the underlying
table(s) are reflected in the cursor.
·
Dynamic cursors
don’t support all properties and methods implemented by the database API
cursors. Properties such as AbsolutePosition, Bookmark,
and RecordCount, as well as methods such
as clone and Resync, are not supported by dynamic cursors.
Instead, they are supported by keyset-driven cursors.
·
The dynamic
cursor is absolutely the slowest possible cursor to use in all situations. It
takes more locks and holds them longer, which radically increases its poor
performance. Take this into account when designing your system.
Dynamic
Cursor – Example
-- Dynamic Cursor for Update
SET NOCOUNT ON
DECLARE @Id INT
DECLARE @name VARCHAR(50)
DECLARE Dynamic_cur_empupdate CURSOR DYNAMIC
FOR
SELECT EmpID ,
EmpName
FROM Employee
ORDER BY EmpName
OPEN Dynamic_cur_empupdate
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id, @name
WHILE @@Fetch_status = 0
BEGIN
IF @name = 'Mohan'
UPDATE Employee
SET Salary = 15000
WHERE CURRENT OF Dynamic_cur_empupdate
FETCH NEXT FROM Dynamic_cur_empupdate INTO @Id, @name
END
END
CLOSE Dynamic_cur_empupdate
DEALLOCATE Dynamic_cur_empupdate
SET NOCOUNT OFF
Go
SELECT *
FROM Employee
-- Dynamic Cursor for DELETE
SET NOCOUNT ON
DECLARE @Id INT
DECLARE @name VARCHAR(50)
DECLARE Dynamic_cur_empdelete CURSOR DYNAMIC
FOR
SELECT EmpID ,
EmpName
FROM Employee
ORDER BY EmpName
OPEN Dynamic_cur_empdelete
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id, @name
WHILE @@Fetch_status = 0
BEGIN
IF @name = 'Deepak'
DELETE Employee
WHERE CURRENT OF Dynamic_cur_empdelete
FETCH NEXT FROM Dynamic_cur_empdelete INTO @Id, @name
END
END
CLOSE Dynamic_cur_empdelete
DEALLOCATE Dynamic_cur_empdelete
SET NOCOUNT OFF
Go
SELECT *
FROM Employee
4) READ_ONLY vs. SCROLL_LOCKS vs. OPTIMISTIC: specify concurrency of a cursor. Default to READ_ONLY.
READ_ONLY – cannot update data through the cursor
Prevents updates 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 – can update/delete data with success
as it locks the data when the cursor reads the data
Specifies that positioned updates or deletes made through the
cursor are guaranteed to succeed. 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 or STATIC is also specified.
OPTIMISTIC – do not lock the data. If the row has
NOT been updated since it was read into the cursor, then the update/delete can
be done through the cursor. If the row has been updated (through other ways)
since it was read into the cursor, then the update/delete through the cursor
will fail.
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.
5) TYPE_WARNING
Specifies that a warning message is sent to the client when the
cursor is implicitly converted from the requested type to another.
DECLARE SOID CURSOR GLOBAL STATIC TYPE_WARNING
FOR
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE OnlineOrderFlag = 1;
6) 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 select_statement of a cursor declaration.
Note: You can use a query hint within a cursor
declaration; however, if you also use the FOR UPDATE OF clause, specify OPTION
(query_hint) after FOR UPDATE
OF.
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.
7) FOR 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.
The following example is
from How to Use Update Cursors in SQL Server
on http://www.codeproject.com/Articles/232452/How-to-Use-Update-Cursors-in-SQL-Server
There
can be a situation where you have to use a cursor, even though the experts say
not to use cursors or to avoid them as much as possible. But if you look
closely, most of the time we use cursors to iterate through a row collection
and update the same table.
In these
type of situations, it is ideal to use an Update Cursor, than using the default
read only one.
Consider
the following table:
CREATE TABLE [dbo].[SAMPLE_EMPLOYEE](
[EMP_ID] [int] NOT NULL,
[RANDOM_GEN_NO] [VARCHAR](50) NULL
) ON [PRIMARY]
Insert
few records to the above table using the following script:
SET NOCOUNT ON
DECLARE @REC_ID AS INT
SET @REC_ID = 1
WHILE ( @REC_ID <= 1000 )
BEGIN
INSERT INTO SAMPLE_EMPLOYEE
SELECT @REC_ID ,
NULL
IF ( @REC_ID <= 1000 )
BEGIN
SET @REC_ID = @REC_ID + 1
CONTINUE
END
ELSE
BEGIN
BREAK
END
END
SET NOCOUNT OFF
Next, we
will add a Primary Key using the below script (or you can use the table
designer):
ALTER TABLE [dbo].[SAMPLE_EMPLOYEE]
ADD CONSTRAINT [PK_SAMPLE_EMPLOYEE] PRIMARY KEY CLUSTERED
(
[EMP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
**
Please note: A primary key should be there if we are to use an update cursor.
Otherwise the cursor will be read only.
Here is how you use the Update Cursor. I have highlighted the
areas which differ compared with a normal cursor. You have to mention which
column you are going to update (or all columns in your selection will be
updatable) and you have to use ‘where current of <cursor>’
in your update statement.
SET NOCOUNT ON
DECLARE @EMP_ID AS INT ,
@RANDOM_GEN_NO AS VARCHAR(50) ,
@TEMP AS VARCHAR(50)
DECLARE EMP_CURSOR CURSOR
FOR
SELECT EMP_ID ,
RANDOM_GEN_NO
FROM
SAMPLE_EMPLOYEE FOR UPDATE OF RANDOM_GEN_NO
OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR
INTO @EMP_ID, @RANDOM_GEN_NO
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SELECT
@TEMP = FLOOR(RAND() * 10000000000000)
UPDATE
SAMPLE_EMPLOYEE
SET
RANDOM_GEN_NO = @TEMP
WHERE CURRENT OF EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR
INTO @EMP_ID, @RANDOM_GEN_NO
END
CLOSE EMP_CURSOR
DEALLOCATE EMP_CURSOR
