Cascading query with parameters

I need to get at some information and this is the only way I know how to do it but I need to run these queries in order and using the previous query output. I don't know how to set up the output parameters so they can be used in the second query and the second query results to be used in the third query.

In a nutshell: The first query gets a number from a user as a parameter and pulls one record containing multiple fields. Some of those fields will be needed as parameters in the second query to pull mutltiple records. I need to put some where statements in to narrow down the result set. Then this part I haven't figured out either, but then the third query takes one record from the second result set and pulls mutliple records to put in a report.

I hope at least a good part of this makes sense. Please HELP ME!

ALTER PROCEDURE sp_getOfficerList

@.compositeNumber int

as

SELECT tblCampus.fldCampusCode, tblGroup.fldGroupCode, tblContract.fldGraduationMonthCode, tblComposite.fldCompositeNumber,

tblContract.fldContractCode, tblGroup.fldGroupName, tblCampus.fldCampusName

FROM tblComposite INNER JOIN

tblContract ON tblComposite.fldContractID = tblContract.fldContractID INNER JOIN

tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN

tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID INNER JOIN

tblCampus ON tblOrganization.fldCampusID =tblCampus.fldCampusID

WHERE (tblComposite.fldCompositeNumber = @.compositeNumber)

declare

@.campusCode varchar(10),

@.groupCode varchar(5),

@.graduationMonthCode varchar(2)

SELECT tblComposite.fldCompositeNumber

FROM tblContract INNER JOIN

tblComposite ON tblContract.fldContractID = tblComposite.fldContractID INNER JOIN

tblOrganization ON tblContract.fldOrganizationID = tblOrganization.fldOrganizationID INNER JOIN

tblCampus ON tblOrganization.fldCampusID = tblCampus.fldCampusID INNER JOIN

tblGroup ON tblOrganization.fldGroupID = tblGroup.fldGroupID

WHERE (tblCampus.fldCampusCode = @.campusCode) AND (tblGroup.fldGroupCode = @.groupCode) AND

(tblContract.fldGraduationMonthCode = @.graduationMonthCode)

declare

@.lastCompositeNumber int

SELECT DISTINCT tblCameraCard.fldTitle

FROM tblCameraCard INNER JOIN

tblComposite_CameraCard_Link ON tblCameraCard.fldCameraCardID = tblComposite_CameraCard_Link.fldCameraCardID INNER JOIN

tblComposite ON tblComposite_CameraCard_Link.fldCompositeID = tblComposite.fldCompositeID

WHERE (tblComposite.fldCompositeNumber = @.lastCompositeNumber) AND (NOT (tblCameraCard.fldTitle IS NULL)) OR

(tblCameraCard.fldTitle = '')

Ok, let me rephrase my question. I want to take the result from query A and make it be the parameter for query B and take the result from query B and make that the parameter for query C. The results from query C is what I want to put in my report.

I did some research and found I could set a query equal to something. So I did that but how do I get that returned value to the next query?