我如何加入存储过程？

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.

我想使用它返回的记录集和查询，我需要在租户的id上加入它的结果。

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?

我怎样才能做到这一点？

8 个解决方案

#1

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），但是如果由于某种原因你被绑在SP本身上，你可以用它来填充临时表，然后加入临时表。请注意，您将在那里花费一些额外的开销，但这是我能够想到使用实际存储过程的唯一方法。

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

同样，您最好将查询从SP内嵌到原始查询中。

#2

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

将SP的结果插入临时表，然后加入：

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

#3

The short answer is "you can't". What you'll need to do is either use a subquery or you could convert your existing stored procedure in to a table function. Creating it as function would depend on how "reusable" you would need it to be.

简短的回答是“你不能”。您需要做的是使用子查询，或者您可以将现有的存储过程转换为表函数。将其创建为函数将取决于您将需要它的“可重用”方式。

#4

Your stored procedure could easily be used as a view instead. Then you can join it on to anything else you need.

您的存储过程可以轻松地用作视图。然后，您可以将其加入到您需要的任何其他内容中。

SQL:

SQL：

CREATE VIEW vwTenantBalance
AS

 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

The you can do any statement like:

你可以做任何声明，如：

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

#5

I resolved this problem writing function instead of procedure and using CROSS APPLY in SQL statement. This solution works on SQL 2005 and later versions.

我解决了这个问题，编写函数而不是过程，并在SQL语句中使用CROSS APPLY。此解决方案适用于SQL 2005及更高版本。

Gediminas Bukauskas

Gediminas Bukauskas

#6

It has already been answered, the best way work-around is to convert the Stored Procedure into an SQL Function or a View.

它已经得到解答，最好的解决方法是将存储过程转换为SQL函数或视图。

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.

如上所述，简短的答案是您不能直接在SQL中加入存储过程，除非您使用存储过程的输出创建另一个存储过程或函数到临时表并加入临时表，如上所述。

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.

我将通过将您的存储过程转换为SQL函数来回答这个问题，并向您展示如何在您选择的查询中使用它。

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

现在使用该功能，在你的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.

如果您希望从上面的SQL中将参数传递到您的函数中，那么我建议您使用CROSS APPLY或CROSS OUTER APPLY。

Read up on that here.

在这里阅读。

Cheers

干杯

#7

I hope your stored procedure is not doing a cursor loop!

我希望你的存储过程没有做一个游标循环！

If not, take the query from your stored procedure and integrate that query within the query you are posting here:

如果没有，请从存储过程中获取查询，并将该查询集成到您在此处发布的查询中：

SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, t.Memo,
        u.UnitNumber,
        p.PropertyName
        ,dt.TenantBalance
FROM tblTenant t
    LEFT JOIN tblRentalUnit u ON t.UnitID = u.ID
    LEFT JOIN tblProperty   p ON u.PropertyID = p.ID
    LEFT JOIN (SELECT ID, SUM(ISNULL(trans.Amount,0)) AS TenantBalance
                   FROM tblTransaction
                   GROUP BY tenant.ID
              ) dt ON t.ID=dt.ID
ORDER BY p.PropertyName, t.CarPlateNumber

If you are doing something more than a query in your stored procedure, create a temp table and execute the stored procedure into this temp table and then join to that in your query.

如果您在存储过程中执行的操作不仅仅是查询，请创建临时表并将存储过程执行到此临时表中，然后再连接到查询中的那个。

create procedure test_proc
as
  select 1 as x, 2 as y
  union select 3,4 
  union select 5,6 
  union select 7,8 
  union select 9,10
  return 0
go 

create table #testing
(
  value1   int
  ,value2  int
)

INSERT INTO #testing
exec test_proc


select
  *
  FROM #testing

#8

Why not just performing the calculation in your SQL?

为什么不在SQL中执行计算？

SELECT 
  t.TenantName
  , t.CarPlateNumber
  , t.CarColor
  , t.Sex
  , t.SSNO
  , t.Phone
  , t.Memo
  , u.UnitNumber
  , p.PropertyName
  , trans.TenantBalance
FROM tblTenant t
     LEFT JOIN tblRentalUnit u ON t.UnitID = u.ID
     LEFT JOIN tblProperty p ON u.PropertyID = p.ID
     INNER JOIN (
       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
     ) trans ON trans.ID = t.ID
ORDER BY 
  p.PropertyName
  , t.CarPlateNumber