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