Skip to main content

Cursors

  • Use cursors only when absolutely necessary.
  • If the function performed by the cursor could have been achieved by another SQL function e.g. PIVOT or Common Table Expression then rather do that as CURSORS are expensive.
  • When using a cursor to only cycle once through records without updating them, use the following syntax to make the cursor as light as possible:
DECLARE @SchemaID INT

DECLARE curs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
  SELECT
    SchemaID
  FROM cfgSchemaID WITH(NOLOCK)
  WHERE
    GETDATE() BETWEEN ValidFrom AND ValidTo
    AND SysID = 101

OPEN curs

FETCH NEXT FROM curs 
INTO @SchemaID

WHILE @@FETCH_STATUS = 0
BEGIN

     /*Do your commands for @SchemaID here*/ 

/*Get the next author.*/
FETCH NEXT FROM curs 
INTO @SchemaID
END

CLOSE curs
DEALLOCATE curs
  • When evaluating the use of cursors first consider the use of for XML path to loop through each item in a table 
Back to top