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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How can I join on a stored procedure?

Examples related to sql

Examples related to sql-server-2005-express