How to Perform SQL Server Row-by-Row Operations Without Cursors

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

Continues…