Microsoft EMS

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 @timesheets TABLE

(

id int identity,

timesheet_id int

)

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

INSERT INTO @timesheets

(

timesheet_id

)

SELECT

time_sheet_id

FROM

time_sheet ts

INNER JOIN billing_code bc ON ts.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 @max INT

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 @max INT

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_id FROM @timesheets WHERE id = @count

— do stuff here

SET @count = @count + 1

END