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?
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
...