What is row by row processing ?

Question Posted / devesh

2 Answers
7455 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What is row by row processing ?..

Answer / bhaskar

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.

Is This Answer Correct ?

7 Yes

2 No

What is row by row processing ?..

Answer / charles

which I must declare after read is , some times we can not
use other methods to replace cursor ,it's flexiblity is
beyond compare to Loop structure.

But,still,your good advice is very good! Thank you

Is This Answer Correct ?

0 Yes

0 No

Post New Answer