SQL return vs. create table on case

Question

I have a Stored Procedure which right now returns a (potentially) large data set. This data is from a recursive call so it looks something like this:

With Hierarchy as(

SELECT distID as D, [Name] as N, enrolling as E, parent_code as P, 
        FROM sales_reps
        WHERE enrolling = @dist_ID
        UNION ALL
            SELECT
            t.distID as D, t.[Name] as N, t.enrolling as E, t.parent_code as P
        FROM sales_reps t
            INNER JOIN Hierarchy h ON t.enrolling = h.D
)
SELECT *, CASE WHEN P > 5 Then 1 Else 0 END As Q 
From Hierarchy

OK, so that's not too big of a deal, the problem is now I need to use this data a lot more and will be doing multiple queries on this for a couple minutes. So the plan is to sometimes (in order to not break current functionality) create a table with this data that will be deleted later.

I'm trying something like:

CASE @temp_Table
    WHEN IS NULL
        Select * FROM retTable
    ELSE
        SELECT INTO @temp_Table
        * FROM retTable
End 

But I can't get it to let me name the results retTable or like the case statement. I don't think this is a proper use of a case statement, but I don't know what else to use.

Answer 1

This is the best text I've found about how to share data between stored procedues. There is a lot of information in it but it's worth a read:

http://www.sommarskog.se/share_data.html

Not knowing the full details of how often and in what scenarios you want to save this data to a table there are a few options you could use.

If you're only needing to save this information in one place, a simple INSERT-EXEC might work for you. You create a temp table in the calling procedure that matches the output of the called procedure (which I'm calling hierarchy_stored_proc)

CREATE TABLE #results (...)
INSERT INTO #results 
 EXEC hierarchy_stored_proc

If you think you'll need this output in more places, you could change the called procedure to always write data to the temp table, and then add a paramter to the proc that would indicate if it should return the result set via a select statement. If you make the default value of that procedure 1 then existing functionality shouldn't be affected. Something like:

WITH Hierarchy AS (...)
INSERT INTO #results
SELECT ...

IF @returnResults=1
  SELECT * FROM #results