It has already been answered, the best way work-around is to convert the Stored Procedure into an SQL Function or a View.
The short answer, just as mentioned above, is that you cannot directly JOIN a Stored Procedure in SQL, not unless you create another stored procedure or function using the stored procedure's output into a temporary table and JOINing the temporary table, as explained above.
I will answer this by converting your Stored Procedure into an SQL function and show you how to use it inside a query of your choice.
CREATE FUNCTION fnMyFunc()
RETURNS TABLE AS
RETURN
(
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
)
Now to use that function, in your SQL...
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
LEFT JOIN dbo.fnMyFunc() AS a
ON a.TenantID = t.TenantID
ORDER BY p.PropertyName, t.CarPlateNumber
If you wish to pass parameters into your function from within the above SQL, then I recommend you use CROSS APPLY
or CROSS OUTER APPLY
.
Read up on that here.
Cheers