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







        time_sheet ts

        INNER JOIN billing_code bc ON ts.billing_code = bc.billing_code


        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:


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


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)





    — my own loop

    SET @count = 1

    WHILE @count <= @max


        SELECT @timesheet_id = timesheet_id FROM @timesheets WHERE id = @count

        — do stuff here


        SET @count = @count + 1