• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2075
  • 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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