How can I join on a stored procedure?
SqlSql Server-2005-ExpressSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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
...
Solution 2 - Sql
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.
Solution 3 - Sql
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.
Solution 4 - Sql
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
Solution 5 - Sql
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
Solution 6 - Sql
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.
Solution 7 - Sql
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
Solution 8 - Sql
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
Solution 9 - Sql
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