How to execute stored procedures and use their returned table within another procedure?

Question

In MS Access you can do the following using both tables and SELECT queries

FROM Product INNER JOIN outputQry ON Product.ProductId = outputQry.ProductId

Assuming Product is a table and outputQry a query, how to do the same in T-SQL ?

I wrote a function that returns a table, how do I execute it within another function/procedure so I can (for example) INNER JOIN the result with some tables?

My function looks like this

ALTER FUNCTION [dbo].[PreconfiguredConfigs_GetNoOfOutputs]()
RETURNS @outputTable TABLE 
    (
         ProductId int,
     NumberOfOutputs int 
    )
AS
BEGIN
DECLARE @table TABLE 
    (
        ProductId int,
        NumberOfOutputs int 
    )
    INSERT INTO @table
    SELECT Outputs.ProductId, Count(Outputs.ProductId) AS NumberOfOutputs
    FROM [dbo.PreconfiguredConfigs].[Outputs]
    GROUP BY Outputs.ProductId;
    INSERT INTO @outputTable
    SELECT ProductId, NumberOfOutputs FROM @table
    RETURN
END

edit: one additional requirement would be that I can 'chain' the functions, so the results from one written above can be used in the another function (INNER JOINing it with other columns), and that function results could be used as well in another function (JOINing these as well).

Can it even be done or do I have to drastically change my designs? The requirements come from the way the database was designed in Access.

This poster suggested to put the SELECT query inside a table-value function, which I did, but I do not understand this line:

SELECT * INTO CustList FROM CustomersbyRegion(1)

what is CustList? Is it a table in the database that gets overwritten whenever the function gets called? I guess that would be a solution, because then I could INNER JOIN it all I want and avoid all the Invalid column name errors I've been seeing when using variables and temporary tables (more on that in my comment to Rick S)

Thank you for the suggestions but I'm pretty sure none of the solutions there help me. The selected answer falls over when I try to reuse my procedure again - creating two levels of EXEC, which I found out the hard way that it can not be done. I can't use OPENROWSET, as someone there commented it's not a right solution but a 'hack', and on top of that I can't open connections to the database, because I don't have the necessary rights on the server... Maybe I should include that info in my question, but at the time of posting I had no way of knowing that would be relevant to it. — kacpr, Apr 30 '14 at 13:46

Rick S · Accepted Answer · 2018-06-18T15:51:50.997

3

You have a couple options:

1) Execute your stored procedure into a temp table and then join with the temp table

CREATE TABLE #tempTable
(
   COL1 INT,
   COL2 INT   
)

INSERT INTO #tempTable 
Exec myStoredProcedure

2) Create a user defined function that returns a table and join on that.

CREATE FUNCTION output
(  

)
RETURNS TABLE 
AS
RETURN 
  SELECT Outputs.ProductId, Count(Outputs.ProductId) AS NumberOfOutputs
  FROM [dbo.PreconfiguredConfigs].[Outputs]
  GROUP BY Outputs.ProductId
GO

edited Jun 18 '18 at 15:51

answered Apr 29 '14 at 15:40

Rick S

6,476
5
29
43

1

1) when I try to use #temptable `INNER JOIN #temptable ON Product.ProductId = #temptable.ProductId` I get `Invalid column name` on #temptable.ProductId 2) That's a partial answer, because even though calling the function returns me a table I still need to put it in a #tempTable or variable @tempTable and that takes me back to the problem in 1) – kacpr Apr 30 '14 at 13:35
1

Example 1 is not working – htafoya Jun 18 '18 at 14:59

score 2 · Answer 2 · answered Apr 29 '14 at 17:00

I believe that what Access is doing when you run a query is to create a "view" which is why you can then join to it. In SQL Server views act in the same way (any changes to the base tables are represented in the view at the time of calling it).

create view output_vw
as
SELECT Outputs.ProductId, Count(Outputs.ProductId) AS NumberOfOutputs
FROM [dbo.PreconfiguredConfigs].[Outputs]
GROUP BY Outputs.ProductId

Then you can join to this:

FROM Product INNER JOIN output_vw ON Product.ProductId = output_vw.ProductId

How to execute stored procedures and use their returned table within another procedure?

2 Answers2