SQL Cursor

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

The basic syntax of a cursor is:

Declare @vStyleID varchar(50)

Declare @Qty numeric

set @Qty = 0

DECLARE CurClosing CURSOR FOR

select DISTINCT vStyleID from tmprptstylewiseclosingreport

OPEN CurClosing

FETCH NEXT FROM CurClosing

INTO @vStyleID

WHILE @@FETCH_STATUS = 0

BEGIN

set @Qty = (select sum(iTotalQty) from vwAutogenID where vStyleID = @vStyleID)

update tmprptstylewiseclosingreport

set nOrderQty = @Qty

where vStyleID = @vStyleID

set @Qty = 0

FETCH NEXT FROM CurClosing

INTO @vStyleID

end

CLOSE CurClosing

DEALLOCATE CurClosing

Based on the example above, cursors include these components:

• DECLARE statements – Declare variables used in the code block

• SET\SELECT statements – Initialize the variables to a specific value

• DECLARE CURSOR statement – Populate the cursor with values that will be evaluated

NOTE – There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.

• OPEN statement – Open the cursor to begin data processing

• FETCH NEXT statements – Assign the specific values from the cursor to the variables

NOTE – This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement

• WHILE statement – Condition to begin and continue data processing

• BEGIN…END statement – Start and end of the code block

NOTE – Based on the data processing multiple BEGIN…END statements can be used

• Data processing – In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic

• CLOSE statement – Releases the current data and associated locks, but permits the cursor to be re-opened

• DEALLOCATE statement – Destroys the cursor

Categories: MSSQL