Answer Posted / 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 |
Post New Answer View All Answers
Mention the differences between substr and charindex in sql server.
What are the different types of collation sensitivity in sql server?
How do I start sql server agent automatically?
What is the library index called?
Write the queries for commands like Create Table, Delete table, Drop Table etc.
What is Extended user-defined?
What are different types of replication in sql server?
What is database white box testing?
What is autocommit mode in sql server?
How to list all objects in a given schema?
What is plan freezing?
What is page-level compression?
What is primary key index?
What is a join in sql? What are the types of joins?
Do you know what is fill factor and pad index?
