Member Avatar for 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"

  • 2 Contributors
  • 4 Replies
  • 198 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by m_shanak
Member Avatar for sknake

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

Member Avatar for m_shanak

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

Yes.exactly

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.