Very Computer

Forgive my ignorance.  I want to pass a few variables to a stored procedure,
ask the stored procedure to run three different SELECT statements (with a
single value each), then return the results of all three as one resultset to
be used with an ASP page.

My attempt is shown below.  While this works in Query Analyzer, it does so
by returning three separate recordsets:

-----------------------------------------

CREATE PROCEDURE userSP_sysTeacherAttainMasteryCount

AS

SELECT     COUNT(*) AS RedMasteryCount
FROM         tblProgress INNER JOIN
                       tblEnrollment ON tblProgress.StudentID =
tblEnrollment.StudentID

SELECT     COUNT(*) AS YellowMasteryCount
FROM         tblProgress INNER JOIN
                       tblEnrollment ON tblProgress.StudentID =
tblEnrollment.StudentID

SELECT     COUNT(*) AS GreenMasteryCount
FROM         tblProgress INNER JOIN
                       tblEnrollment ON tblProgress.StudentID =
tblEnrollment.StudentID


GO

---------------------------------------

Any ideas?

Thanks,
--Gary

Perhaps a better way to do this than produce three resultsets would be to
have three output parameters in the stored proc and use an ADO Command
object to execute it and read the output values from the Command object's
Parameters collection.  The stored proc would look like:

CREATE PROCEDURE userSP_sysTeacherAttainMasteryCount

AS

SELECT     COUNT(*) AS RedMasteryCount
FROM         tblProgress INNER JOIN
                       tblEnrollment ON tblProgress.StudentID =
tblEnrollment.StudentID

SELECT     COUNT(*) AS YellowMasteryCount
FROM         tblProgress INNER JOIN
                       tblEnrollment ON tblProgress.StudentID =
tblEnrollment.StudentID

SELECT     COUNT(*) AS GreenMasteryCount
FROM         tblProgress INNER JOIN
                       tblEnrollment ON tblProgress.StudentID =
tblEnrollment.StudentID


GO

Otherwise, you need to use the NextRecordset method of the ADO Recordset
object to get all three values, you should probably include SET NOCOUNT ON
at the top of the stored proc (after AS) to prevent the rows affected
messages from showing up as result sets themselves.

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

Use a GROUP BY MasteryLevel, this helps you get a single Resultset
with MasteryLevel 1,2,3 etc with corresponding COUNTs

CREATE PROCEDURE userSP_sysTeacherAttainMasteryCount



AS
SELECT  tblProgress.MasteryLevel,
 COUNT(*) AS MasteryCount
FROM
 tblProgress INNER JOIN
        tblEnrollment ON tblProgress.StudentID = tblEnrollment.StudentID
WHERE

AND

AND

GROUP BY
 tblProgress.MasteryLevel

- Anith

(tblEnrollment.Designation=
(tblEnrollment.Designation
(tblEnrollment.Designation

Gary,

    Why not simply use a UNION query to concatenate the recordsets into a
single recordset like so...
SELECT     COUNT(*) AS RedMasteryCount

(tblEnrollment.Designation=
    union

(tblEnrollment.Designation
    union

(tblEnrollment.Designation

(tblEnrollment.Designation=
(tblEnrollment.Designation
(tblEnrollment.Designation