如何加入存储过程? [英] How can I join on a stored procedure?

问题描述

我有一个不带参数的存储过程，它返回两个字段.存储过程将汇总应用于租户的所有交易，并返回余额和租户的ID.

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.

我想使用它随查询返回的记录集，并且我需要将其结果加入到租户的ID中.

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.

这是我当前的查询:

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

存储过程是这样的:

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.

我该怎么做?

推荐答案

我实际上很喜欢上一个答案(不使用SP)，但是如果由于某种原因与SP本身联系在一起，则可以使用它填充临时表，然后加入临时表.请注意，您将在那里花费一些额外的开销，但这是我想到使用实际存储过程的唯一方法.

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.

同样，您最好将SP中的查询内联到原始查询中.

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

这篇关于如何加入存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！