How can I join on a stored procedure?

问题:

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?

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

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

回答2:

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

回答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:

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.

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.

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

回答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?

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

回答9:

Here's a terrible idea for you.

Use an alias, create a new linked server from your server to its own alias.

Now you can do:

select a.SomeColumns, b.OtherColumns
from LocalDb.dbo.LocalTable a
inner join (select * from openquery([AliasToThisServer],'
exec LocalDb.dbo.LocalStoredProcedure
') ) b
on a.Id = b.Id