Cursors, BAD! Table Vars, GOOD!

As of SQL Server 2000, we have had the table variable to use in T-SQL for temporary tables or user defined functions. I like to use table variables wherever I can because I have observed performance problems with CURSORS and #TEMPTable objects.

Stephen Livingstone recently posted some T-SQL that used CURSOR objects to iterate through rows and call another stored procedure. Here is his code:

DECLARE MYCURSOR CURSOR FOR
  (SELECT p1, p2
   FROM  tbl_MyTable
   WHERE p4=123)

 DECLARE @p1 INT
 DECLARE @p2 INT
 DECLARE @p3 INT

 OPEN MYCURSOR
  FETCH NEXT FROM MYCURSOR INTO @p1, @p2, @p3

  WHILE @@FETCH_STATUS = 0
   BEGIN

    -call stored procedure
    exec UpdateTable @p1, @p2, @p3

    FETCH NEXT FROM MYCURSOR

   END

    CLOSE MYCURSOR
    DEALLOCATE MYCURSOR

Here is how I would prefer to write this, assuming that I had to call the internal stored procedure iteratively:

-- set up my in-memory table
declare @myTable table
   (rowId int identity(1,1),
   p1 int,
   p2 int,
   p3 int)

-- set up row counters and “cursor“ variables
declare @rowId int, @maxRowId int, @p1 int, @p2 int, @p3 int

-- insert the rows into the work table
insert into @myTable
   (p1, p2, p3)
select
   p1, p2, p3
from
   tbl_myTable
where
   p4 = 123

-- determine the start and end rows (RowId is an Identity column, auto-incrementing starting at 1)
-- note, I should check for Null here.
select
   @rowId = min(RowId),
   @MaxRowId = max(RowId)
from @myTable

-- here is the loop
while @RowId <= @MaxRowId begin
   -- fetch the values from the work table
   -- for the current row
   select
      @p1 = p1,
      @p2 = p2,
      @p3 = p3
   from
      @myTable
   where 
      RowId = @RowId

   -- call the stored proc for this row
   exec UpdateTable @p1, @p2, @p3
   -- increment the row counter
   set @RowId = @RowId + 1
end

-- end of procedure

I'm not exactly sure what SQL Server is doing behind the scenes, but here is my theory - @Table variables use a lot less of tempdb (or maybe none at all) than do #TEMPTable and CURSOR objects. I know that #tempTable objects are created in tempdb, and I think that CURSOR objects are created there too. In some performance issues I was troubleshooting with stored procedures, they were deadlocking on tempdb..sysobjects and tempdb..syscolumns, and when I re-wrote them to use @table variables instead of CURSORS, those problems went away.

There are some limitations of @table variables - they are completely locally scoped, they cannot be passed as parameters, and they cannot use INSERT INTO @table EXEC StoredProc. See here and here for some earlier discussion about @Table variable scope.

Mike

PS: My first preference, if this code is being called regularly, is to revisit the stored proc UpdateTable, and either create another stored proc that does the same thing but operates on a set of rows (passing in the criteria for P4, in the example the value for P4 was 123).

CREATE PROC dbo.UpdateTableSet @filter int
as

update tbl_myOthertable
  set ...
from tbl_MyOtherTable mot
join tbl_myTable mt on mot.MotId = mt.ModId
where pt.P4 = @filter

Update: I fixed a boundary bug in the While statement, as pointed out by