How to joint Proc. results with an aggregate (1 Viewer)

[SmallTime]

Hi

I need some help working out how I could join the results from a procedure together with a Sum from another table (perhaps it could that be done from within a single procedure?)

This is what I have for the proc with variables

CREATE PROC [dbo].[CASES]

	  @Site varchar(100) = NULL
	, @Closed varchar(3) = "NO"
as
	SET NOCOUNT ON;
	if @Site is null
		SELECT rID, rSite, rAddress
		from Tblr
		WHERE rPerson IS NOT NULL AND rClosed  = @Closed

	Else
		SELECT rID, rSite, rAddress
		FROM Tblr
		WHERE rPerson IS NOT NULL AND rSite = @Site AND rClosd = @Closed

I’d like to show the the results of the above and to include a SUM Column from a many sided related table. The related field to Tblr.rID (above) is Tblc.crID. The field I need to SUM in Tblc is cCost and gouped by crID.

Much appreciated if someone would point me in the right direction.

 

[SmallTime]

Managed to worked it out.

In case someone else needs the answer this is what I did.

1 Create a view from Tblc and do the calculations there i.e.
SUM(cCost) and Group on crID

2 Create the procedure (as above) + an inner join to the newly created view.

3 Execute

Bob's your uncle, Fanny's your aunt