How can I join on a stored procedure?

Question

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?

Your question and your sample code don't tie up - you talk about transactions and users; the sample is about tenants and rental properties. — Jonathan Leffler, May 28 '09 at 14:41
tenants = users. I'm wanting to add balance to the query because it's for a report, and it needs all the information about the property the tenant lives at too. — Malfist, May 28 '09 at 14:43
Also, why would you use a LEFT OUTER JOIN between your rental unit and property tables? You shouldn't let your database be in such a state of referential disintegrity that there could be a need for it (you should have a foreign key constraint between rental unit and property that prevents invalid codes in rental unit). — Jonathan Leffler, May 28 '09 at 14:43
Sorry, that was a typo. Thanks for catching that. The tables are linked Tenant -> Rental Unit -> Property. — Malfist, May 28 '09 at 14:45
Your description of the SP implies it returns one row - but I take it from the further discussion that it returns a result set with one row for each tenant. Effectively, it returns a table. Since you're using SQL Server, I don't know whether there is a way to make it treat the result set from a SP as a table. In at least one other DBMS, you can use TABLE(MULTISET(EXECUTE FUNCTION spname())) AS Alias(Balance, ID) as part of the FROM clause. — Jonathan Leffler, May 28 '09 at 15:31
Yes, it returns a record set (which is what the question specifies), one row per tenant. — Malfist, May 28 '09 at 15:43
I think showing your table structures rather than your sample code may help potentially solve your problem. — DJ., May 28 '09 at 16:20
@DJ, the problem is solved. I'm not wanting a code answer, I'm wanting to know how to do it. That way I learn instead of someone else doing the work for me. — Malfist, May 28 '09 at 16:27
@Malfist - my answer provides the best solution to your problem (wanting to have a single piece of SQL that calculates the tenant balance and join it in otehr queries) — cjk, May 29 '09 at 07:19
Guys! So what was the real answer? All the "answers" below are work arounds by avoiding the use of a stored procedure DIRECTLY into the SQL required. You cannot use CROSS APPLY on a stored procedure, yet a SP can return tables, so why not!? — Fandango68, May 27 '16 at 01:26

score 61 · Answer 1 · answered May 28 '09 at 15:03

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

score 31 · Accepted Answer · answered May 28 '09 at 14:55

31

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.

answered May 28 '09 at 14:55

AllenG

8,112
29
40

6

I'd rather not inline it, because then if I do change the SP, I'll have to hunt down everywhere I've inlined it and change it too – Malfist May 28 '09 at 14:59
1

@Malfist, if you insist on it being a SP instead of a function or inline, your only option is what AllenG suggests, populate a temp table and join on that. – Lieven Keersmaekers May 28 '09 at 15:03
Try using a table variable instead of a temp table. – David Dec 16 '15 at 18:18
2

@AllenG I am sorry but that is not an answer. "Don't use the SP" is avoiding a way to doing it using a CROSS APPLY, but if that doesn't work, then the answer should have been simply "no you cannot do it." full stop. Saying to avoid a SP and change the logic into a function is not the answer – Fandango68 May 27 '16 at 01:28

score 20 · Answer 3 · answered May 28 '09 at 14:53

20

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.

answered May 28 '09 at 14:53

CAbbott

8,078
4
31
38

2

A UDF (User Defined Function) that returns a table would be incredibly useful. – TheTXI May 28 '09 at 15:02
3

You said use a 'sub query'. Well isn't that a solution? Please explain what you meant with some code? Thanks – Fandango68 May 27 '16 at 01:24

cjk · Answer 4 · 2009-05-29T07:11:38.297

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

I think you'll find this is a much better answer than using a temp table as it is still a centralised piece of code that will only ever need to be updated in one place, and has no overhead of creating a temp table, then inserting into it, then selecting back out of it. — cjk, May 28 '09 at 16:07

score 8 · Answer 5 · edited Aug 20 '21 at 09:01

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 is that you cannot directly JOIN a Stored Procedure in SQL, unless you create another stored procedure or function using the stored procedure's output into a temporary table and JOINing the temporary table.

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

score 6 · Answer 6 · edited Oct 15 '20 at 09:43

6

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.

edited Oct 15 '20 at 09:43

ASh

34,632
9
60
82

answered Aug 09 '09 at 10:30

score 2 · Answer 7 · answered Oct 10 '18 at 01:38

2

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

answered Oct 10 '18 at 01:38

Devin Lamothe

182
1
1
11

1

Nice horrible idea, I've never thought of that – Costa Jan 20 '20 at 08:08
What are the risks we take by doing this solution? – Daniel L. VanDenBosch Oct 05 '21 at 20:49

KM. · Answer 8 · 2009-05-28T15:05:13.893

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

I'm asking how to integrate the SP, because I don't know how — Malfist, May 28 '09 at 14:58

score 0 · Answer 9 · answered May 28 '09 at 14:55

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

How can I join on a stored procedure?

9 Answers9

Linked