help in stored procedure-result of a loop using cursor

[m_shanak]

hi every body.

i have a stored procedure with a select statment that looped by a cursor .
this is the code

DECLARE res cursor READ_ONLY 
		for (SELECT DISTINCT GroupNo
			FROM            ppu_MajorPlan
			WHERE        (PlanYear =(SELECT         majorPlanYear
                               FROM     ppu_Student 
                               WHERE    (StudentNo = @StudentNo)))
	
	
			AND (MajorNo =(SELECT         MajorNo
                               FROM    ppu_Student 
                               WHERE   (StudentNo = @StudentNo))) 
			) 
	
	
	
	/* Using The Curser*/
	open res
	FETCH NEXT FROM res
	INTO @GroupID
	


WHILE @@FETCH_STATUS = 0
	BEGIN
		
		SELECT    CourseNo
		FROM            ppu_Grades
		WHERE        (StudentNo = @StudentNo)  AND  (GradeNGrade < 60)
		
		intersect
		
			SELECT        CourseNo
			FROM            ppu_PlanGroupCourse
			WHERE        (GroupNo =@GroupID)
			 AND (MajorPlanYear = (SELECT         majorPlanYear
                               FROM     ppu_Student 
                               WHERE    (StudentNo = @StudentNo))) 			 
			 AND (MajorNo = (SELECT         MajorNo
                               FROM    ppu_Student 
                               WHERE   (StudentNo = @StudentNo)))
				
				
				
		FETCH NEXT FROM res
		INTO @GroupID

	
	END

CLOSE res
DEALLOCATE res
					
					
					
					
				   END

the Question is :how can I store and use the data returned from the intersection ,"the problem i faced is that the stored procedure return the result of the first interesction Not all results from the loop"

[sknake]

You're trying to return the value of multiple select statements in a single stored procedure?

[m_shanak]

You're trying to return the value of multiple select statements in a single stored procedure?

Yes.exactly

[sknake]

How are you viewing the results of the SP? In the query analyzer or in your application? I have tested this concept with the QA and it works fine:

IF OBJECT_ID('sp_GetTablesX', 'P') IS NOT NULL DROP PROCEDURE sp_GetTablesX
--
GO
--
CREATE PROCEDURE sp_GetTablesX
AS
BEGIN
  Declare @lServerId int
  
  DECLARE det_cursor CURSOR FOR
  Select 1 As Col Union All Select 2 Union All Select 3 --3 sample rows
  OPEN det_cursor
  FETCH NEXT FROM det_cursor INTO @lServerId
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    Select @lServerId
    FETCH NEXT FROM det_cursor INTO @lServerId
  END
  
  CLOSE det_cursor
  DEALLOCATE det_cursor
END
--
GO
--
exec dbo.sp_GetTablesX

If you're using an application this is likely a problem in your code or the data adapter when a query returns multiple result cursors. This is not a common task.

[m_shanak]

when i execute this procedure every thing work fine

but when using the .NET (dataset) to show the result in a gridView it show me result of the first select only

this is the result when execut the storedprocedure

http://img98.imageshack.us/img98/6217/53049993.jpg

this is the result on .Net

http://img4.imageshack.us/img4/5130/78427120.jpg