row by row processing by Using Cursors

Row by row processing means for example we can update the 
one column , on that column we have 500 records ,we are 
going to update the all recodes row by row by using cursors.

SQL cursors have been a curse to database programming for 
many years because of their poor performance. On the other 
hand, they are extremely useful because of their 
flexibility in allowing very detailed data manipulations at 
the row level. Using cursors against SQL Server tables can 
often be avoided by employing other methods, such as using 
derived tables, set-based queries, and temp tables. A 
discussion of all these methods is beyond the scope of this 
article, and there are already many well-written articles 
discussing these techniques.The focus of this article is 
directed at using non-cursor-based techniques for 
situations in which row-by-row operations are the only, or 
the best method available, to solve a problem. Here, I will 
demonstrate a few programming methods that provide a 
majority of the cursor’s flexibility, but without the 
dramatic performance hit.

Let’s begin by reviewing a simple cursor procedure that 
loops through a table. Then we’ll examine a non-cursor 
procedure that performs the same task.

if exists (select * from sysobjects where name = 
N’prcCursorExample’)
drop procedure prcCursorExample
go

CREATE PROCEDURE prcCursorExample
AS
/*
** Cursor method to cycle through the Customer table and 
get Customer Info for each iRowId.
**
** Revision History:
** —————————————————
** Date Name Description Project
** —————————————————
** 08/12/03 DVDS Create —-
**
*/

SET NOCOUNT ON

– declare all variables!
DECLARE @iRowId int,
@vchCustomerName nvarchar(255),
@vchCustomerNmbr nvarchar(10)

– declare the cursor
DECLARE Customer CURSOR FOR
SELECT iRowId,
vchCustomerNmbr,
vchCustomerName
FROM CustomerTable
OPEN Customer
FETCH Customer INTO @iRowId,
@vchCustomerNmbr,
@vchCustomerName

– start the main processing loop.

WHILE @@Fetch_Status = 0
BEGIN
– This is where you perform your detailed row-by-row 
processing.

– Get the next row.
FETCH Customer INTO @iRowId,
@vchCustomerNmbr,
@vchCustomerName

END

CLOSE Customer
DEALLOCATE Customer
RETURN
--------------------------
2.row by row processing in general


As you can see, this is a very straight-forward cursor 
procedure that loops through a table called CustomerTable 
and retrieves iRowId, vchCustomerNmbr and vchCustomerName 
for every row. Now we will examine a non-cursor version 
that does the exact same thing:

if exists (select * from sysobjects where name = 
N’prcLoopExample’)
drop procedure prcLoopExample
go

CREATE PROCEDURE prcLoopExample
AS
/*
** Non-cursor method to cycle through the Customer table 
and get Customer Info for each iRowId.
**
** Revision History:
** ——————————————————
** Date Name Description Project
** ——————————————————
** 08/12/03 DVDS Create —–
**
*/

SET NOCOUNT ON

– declare all variables!

DECLARE @iReturnCode int,
@iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@vchCustomerName nvarchar(255),
@vchCustomerNmbr nvarchar(10)
@chProductNumber nchar(30)

– Initialize variables!
SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(iRowId)
FROM CustomerTable

– Make sure the table has data.
IF ISNULL(@iNextRowId,0) = 0
BEGIN
SELECT ‘No data in found in table!’
RETURN
END

– Retrieve the first row

SELECT @iCurrentRowId = iRowId,
@vchCustomerNmbr = vchCustomerNmbr,
@vchCustomerName = vchCustomerName
FROM CustomerTable
WHERE iRowId = @iNextRowId

– start the main processing loop.
WHILE @iLoopControl = 1
BEGIN

– This is where you perform your detailed row-by-row 
processing.
– Reset looping variables.
SELECT @iNextRowId = NULL

– get the next iRowId
SELECT @iNextRowId = MIN(iRowId)
FROM CustomerTable
WHERE iRowId > @iCurrentRowId

– did we get a valid next row id?

IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END

– get the next row.
SELECT @iCurrentRowId = iRowId,
@vchCustomerNmbr = vchCustomerNmbr,
@vchCustomerName = vchCustomerName
FROM CustomerTable
WHERE iRowId = @iNextRowId
END
RETURN

There are several things to note about the above procedure.

For performance reasons, you will generally want to use a 
column like “iRowId” as your basis for looping and row 
retrieval. It should be an auto-incrementing integer data 
type, along with being the primary key column with a 
clustered index.

There may be times in which the column containing the 
primary key and/or clustered index is not the appropriate 
choice for looping and row retrieval. For example, the 
primary key and/or clustered index may have already been 
built on a column using uniqueindentifier as the data type. 
In such a case, you can usually add an auto-increment 
integer data column to the table and build a unique index 
or constraint on it.

The MIN function is used in conjunction with greater 
than “>” to retrieve the next available iRowId. You could 
also use the MAX function in conjunction with less than “<” 
to achieve the same result:

SELECT @iNextRowId = MAX(iRowId)
FROM CustomerTable
WHERE iRowId < @iCurrentRowId

Be sure to reset your looping variable(s) to NULL before 
retrieving the next @iNextRowId value. This is critical 
because the SELECT statement used to get the next iRowId 
will not set the @iNextRowId variable to NULL when it 
reaches the end of the table. Instead, it will fail to 
return any new values and @iNextRowId will keep the last 
valid, non-NULL, value it received, throwing your procedure 
into an endless loop. This brings us to the next point, 
exiting the loop.

When @iNextRowId is NULL, meaning the loop has reached the 
end of the table, you can use the BREAK command to exit the 
WHILE loop. There are other ways of exiting from a WHILE 
loop, but the BREAK command is sufficient for this example.

You will notice that in both procedures I have included the 
comments listed below in order to illustrate the area in 
which you would perform your detailed, row-level processing.