Return cursor from CTE in stored procedure

I have written a stored procedure that produces a list of all child nodes from a hierarchical list using CTE, now i need to get the list of child id's back to the calling procedure. I am trying to use a cursor buy the procedure now fails with the following

Msg 156, Level 15, State 1, Procedure GetAllListHeirs, Line 30
Incorrect syntax near the keyword 'SET'.

Can anybody tell me what I am doing wrong?
Thanks.
ALTER PROCEDURE [dbo].[GetAllListHeirs] 
	-- Add the parameters for the stored procedure here
	@ListID INT = 0,
	@ListCursor CURSOR VARYING OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Put me and all children in the heirs list
WITH HeirsCTE (ListID) AS
	(
	SELECT el.ListID
	FROM dbo.EviList el
	WHERE el.ListID = @ListID
	UNION ALL
--   -- Recursive query
	SELECT el.ListID
	FROM dbo.EviList el
       INNER JOIN HeirsCTE CTE ON CTE.ListID = el.ParentID
	)
 
   SET @ListCursor = CURSOR FORWARD_ONLY STATIC
    FOR
		SELECT * FROM HeirsCTE
	
	OPEN @ListCursor
 
END

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dportasCommented:
The syntax should look like the following. I wouldn't usually recommend using cursors as output parameters because that limits you to row-by-row operations in your calling code. It should be possible to restructure your code in set-based fashion - using a table-valued function for example or moving more of your calling code into one proc.

Since you describe yourself as a beginner I would suggest you to avoid cursors altogether for data manipulation operations. Otherwise you may teach yourself bad habits. 99.99% of the time you don't need cursors so always get a second opinion if you think you have reason to.


SET @ListCursor = CURSOR
    FORWARD_ONLY STATIC
    FOR
WITH HeirsCTE (ListID) AS
      (
      SELECT el.ListID
      FROM dbo.EviList el
      WHERE el.ListID = @ListID
      UNION ALL
--   -- Recursive query
      SELECT el.ListID
      FROM dbo.EviList el
       INNER JOIN HeirsCTE CTE ON CTE.ListID = el.ParentID
      )
      SELECT * FROM HeirsCTE;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John-trying-very-hardAuthor Commented:
Thanks for that Dportas, your code works and I have my cursor. You seem to concur with everyone else about cursors, this method was suggested to me as a way of getting a set of values into a WHERE IN clause, cursor to temp table in the calling procedure then WHERE x IN (SELECT * FROM temp table), but I suspect that the person that suggested it is as newbie as me. Thanks for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.