• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2078
  • Last Modified:

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

0
John-trying-very-hard
Asked:
John-trying-very-hard
1 Solution
 
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;
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now