stored procedure to retrun two result sets where seconed result is related to first

[mcnewsxp]

how can i write a stored procedure that will return 2 result sets.
the second result set will be keyed off of a value (EVENT_ID) in the first result set.
the first result set contains a list of events and the second result set will contain a list of people who will be attending the events.

here's what i have:

SELECT EVENT.EV_EVENT_ID, EVENT.EV_EVENTSTART, EVENT.EV_EVENTEND,
CASE WHEN [EVENT].EV_EVENT_TYPE_ID = 2 THEN EVENT_TYPE.ETP_NAME ELSE EVENT_TYPE.ETP_NAME END AS EV_NAME, EVENT.EV_SchoolID,
SCHOOLS.SCH_Name
FROM EVENT_ATTENDABLE AS EVENT INNER JOIN
SCHOOLS ON EVENT.EV_SchoolID = SCHOOLS.SCH_SCHOOLS_ID LEFT OUTER JOIN
EVENT_TYPE ON EVENT.EV_EVENT_TYPE_ID = EVENT_TYPE.ETP_EVENT_TYPE_ID
WHERE (EVENT.EV_THERAPIST_ID = @EV_THERAPIST_ID) AND (EVENT.EV_EVENTSTART BETWEEN @STARTDATE AND @ENDDATE + 1)

SELECT EVENT_STUDENT_ASSIGNMENTS.EV_EVENT_ID, STUDENTS.STU_STUDENT_ID, STUDENTS.STU_FirstName, STUDENTS.STU_LastName
FROM EVENT_STUDENT_ASSIGNMENTS INNER JOIN
STUDENTS ON EVENT_STUDENT_ASSIGNMENTS.STU_STUDENT_ID = STUDENTS.STU_STUDENT_ID
WHERE EVENT_STUDENT_ASSIGNMENTS.EV_EVENT_ID = ???????

[Bob Barrows]

Post by mcnewsxp
how can i write a stored procedure that will return 2 result sets.
the second result set will be keyed off of a value (EVENT_ID) in the first result set.
the first result set contains a list of events and the second result
set will contain a list of people who will be attending the events.
SELECT EVENT.EV_EVENT_ID, EVENT.EV_EVENTSTART, EVENT.EV_EVENTEND,
CASE WHEN [EVENT].EV_EVENT_TYPE_ID = 2 THEN
EVENT_TYPE.ETP_NAME ELSE EVENT_TYPE.ETP_NAME END
AS EV_NAME, EVENT.EV_SchoolID, SCHOOLS.SCH_Name
FROM EVENT_ATTENDABLE AS EVENT INNER JOIN
SCHOOLS ON EVENT.EV_SchoolID =
SCHOOLS.SCH_SCHOOLS_ID LEFT OUTER JOIN
EVENT_TYPE ON EVENT.EV_EVENT_TYPE_ID = EVENT_TYPE.ETP_EVENT_TYPE_ID
SELECT EVENT_STUDENT_ASSIGNMENTS.EV_EVENT_ID,
STUDENTS.STU_STUDENT_ID, STUDENTS.STU_FirstName,
STUDENTS.STU_LastName
FROM EVENT_STUDENT_ASSIGNMENTS INNER JOIN
STUDENTS ON
EVENT_STUDENT_ASSIGNMENTS.STU_STUDENT_ID = STUDENTS.STU_STUDENT_ID
WHERE EVENT_STUDENT_ASSIGNMENTS.EV_EVENT_ID = ???????

You need to add EVENT_ATTENDABLE to the second query and apply the same
conditions as were applied in the first query.