[sql] 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?

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.

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

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

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

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

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

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.

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