CODEWITHSUNDEEP
sql.netsql-serversql-server-2008
FrenkyB
FrenkyB

Reputation: 7197

Get select results from recursive stored procedure

I have a recursive stored procedure, which looks for errors in my hierarchy table. Since it's recursive, if it finds a bug, it's selected in that recursive call. So, there are numerous select's in recursive calls. My problem is how to catch all those results in .NET application ? I can see this results inside SSMS, but in .NET I see only last result of the latest select.

My procedure (most of the code omitted due to simplicity - I've left just basics and that is recursive selects when bug is found):

CREATE PROCEDURE MyProcedure
@Node HierarchyID,
@Iterator int
AS



IF -- some condition
BEGIN
  SELECT 'ERROR: ' + @ChildNumber + @Node
END

OPEN ChildRowsCursor

WHILE (@Iterator <= 100)
	BEGIN	
		FETCH NEXT FROM ChildRowsCursor INTO @CurrentChildNode
		EXEC dbo.MyProcedure @CurrentChildNode, @Iterator		
		SET @Iterator = @Iterator + 1
	END
	


CLOSE ChildRowsCursor
DEALLOCATE ChildRowsCursor

END

EDIT:

For calling stored procedure, I use table adapter:

DataLayer.MyTableAdapters adapter = new DataLayer.MyTableAdapters();
adapter.GetAllErrors();

Upvotes: 0

Views: 215

Answers (1)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Use temporary table for selecting results:

CREATE PROCEDURE MyProcedure
@Node HierarchyID,
@Iterator int
AS
IF OBJECT_ID('tempdb..#result')  IS NULL
CREATE TABLE #result (res VARCHAR(1000));

IF -- some condition
BEGIN
  INSERT INTO #result VALUES ( 'ERROR: ' + @ChildNumber + @Node);
END

OPEN ChildRowsCursor

WHILE (@Iterator <= 100)
    BEGIN   
        FETCH NEXT FROM ChildRowsCursor INTO @CurrentChildNode
        EXEC dbo.MyProcedure @CurrentChildNode, @Iterator       
        SET @Iterator = @Iterator + 1
    END



CLOSE ChildRowsCursor
DEALLOCATE ChildRowsCursor

SELECT * FROM #result;

END

Upvotes: 1

Related Questions