How can I join on a stored procedure?

Question

I have a stored procedure that takes no parameters, and it returns two fields. The stored procedure sums up all transactions that are applied to a tenant, and it returns the balance and the id of the tenant.

I want to use the record set it returns with a query, and I need to join it's results on the id of the tenant.

This is my current query:

SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, t.Memo,         u.UnitNumber,         p.PropertyName FROM tblTenant t     LEFT JOIN tblRentalUnit u     ON t.UnitID = u.ID      LEFT JOIN tblProperty p     ON u.PropertyID = p.ID  ORDER BY p.PropertyName, t.CarPlateNumber 

The stored procedure is this:

SELECT tenant.ID AS TenantID, SUM(ISNULL(trans.Amount,0)) AS TenantBalance FROM tblTenant tenant     LEFT JOIN tblTransaction trans     ON tenant.ID = trans.TenantID     GROUP BY tenant.ID 

I would like to add the balance from the stored procedure to it also.

How can I do this?

Answer 1

insert the result of the SP into a temp table, then join:

CREATE TABLE #Temp (     TenantID int,      TenantBalance int )  INSERT INTO #Temp EXEC TheStoredProc  SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, t.Memo,     u.UnitNumber, p.PropertyName FROM tblTenant t INNER JOIN #Temp ON t.TenantID = #Temp.TenantID ... 

Answer 2

I actually like the previous answer (don't use the SP), but if you're tied to the SP itself for some reason, you could use it to populate a temp table, and then join on the temp table. Note that you're going to cost yourself some additional overhead there, but it's the only way I can think of to use the actual stored proc.

Again, you may be better off in-lining the query from the SP into the original query.