Let’s face it. We love SQL cursors. At least I do. At least I USED to. Until I started to appreciate the performance price tag associated with one. After that, I have been spending my time finding ways to circumvent the need for a cursor. But what do you do when you have a set of records you absolutely need to perform custom calculations against, and creating a loop seems to be the easiest approach?

I ran into this the other day. I had a table variable that contained nothing but a bunch of record IDs that needed to be evaluated and processed. In this case, an update statement was not going to be convenient enough. I wanted… well, I wanted a cursor. Instead, I created a loop.

Here is my table variable. Notice that I added a column called id that is basically an auto-incrementing identity field. This gives me my loop range

DECLARE@timesheetsTABLE

(

idintidentity,

timesheet_idint

)

 

Here I am populating my table variable. Notice I ignore the id field because it automatically sets an incrementing value for me.

INSERTINTO@timesheets

(

timesheet_id

)

SELECT

time_sheet_id

FROM

time_sheetts

INNERJOINbilling_codebcONts.billing_code = bc.billing_code

WHERE

bc.project_id IN (SELECT project_id FROM @projects)

 

After I am done populating my table variable, I am running a select to show you the id field.

SELECT * FROM @timesheets

 

Here are the results:

Etc….

I can see that the lowerbound of my loop is 1. I use the following statement to determine the upperbound for my loop:

DECLARE@maxINT

SELECT @max = MAX(id) FROM @timesheets

 

Now that I have my loop boundaries, instead of a cursor, I can simply loop through the records.

— get max

DECLARE@maxINT

SELECT@max=MAX(id)FROM @timesheets

IF (ISNULL(@max, 0)= 0)

BEGIN

RETURN

END

 

— my own loop

SET @count = 1

WHILE@count<=@max

BEGIN

SELECT@timesheet_id=timesheet_idFROM@timesheetsWHEREid=@count

— do stuff here

SET @count = @count + 1

END

Advertisements