CTE's and cursors in SP

• SQLServerNewby

  SSC Enthusiast

  Points: 102

  More actions

  November 8, 2007 at 7:29 am

  #376753

  I want to use a CTE in my stored procedure, but I can't figure out how to create a cursor so I can loop through each row returned from the CTE and do some processing.

  I first declare the CTE then I tried creating a cursor based on the CTE but it keeps giving me syntax errors.

  DECLARE @ID int ;

  WITH tree(EntryName, ID, LEVEL) AS (SELECT EntryName, ID, 0

  FROM myTable

  WHERE (EntryName = 'TEST1')

  UNION ALL

  SELECT V.EntryName, V.ID, t . LEVEL + 1

  FROM myTable AS V INNER JOIN

  tree t ON t .ID = V.ParentID)

  DECLARE c1 CURSOR FOR

  SELECT id

  FROM tree

  ORDER BY ID desc;

  OPEN c1

  FETCH next INTO @ID

  WHILE fetch_status != 0

  BEGIN

  -- do some processing here

  FETCH NEXT INTO @ID

  END

  CLOSE c1

  Is this possible in sql server?

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  November 9, 2007 at 6:32 am

  #749819

  try using a while loop ( cursor in disguise )

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/