Why is the #TEMP Table not showing up?

Question

I have a stored procedure as follows that I'd like to export its results in a table.

DECLARE
    @ReportYearCmd VARCHAR(1000),
    @CosCountCmd VARCHAR(1000),
    @FranchiseCountCmd AS VARCHAR(1000),
    @ProductCountCmd AS VARCHAR(1000);

WITH Validations AS (
    SELECT TOP 1 * FROM [Handshake] WHERE [Status] = 'Loaded' AND [Update Time] = ( SELECT MAX( [Update Time] ) FROM Handshake )
)
UPDATE Validations 
SET 
    @ReportYearCmd = CASE WHEN Report_Year_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Report Year] AS [Report Year] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] );'
    END,
    @CosCountCmd = CASE WHEN COS_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] );' 
    END,
    @FranchiseCountCmd = CASE WHEN  Franchise_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Style Code] AS [Franchise - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] );' 
    END,
    @ProductCountCmd = CASE WHEN Product_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Style Code] AS [Product - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] );' 
    END,
    [Status] = CASE
        WHEN ( Report_Year_Count = 0 AND COS_Count = 0 AND Franchise_Count = 0 AND Product_Count = 0 ) THEN 'Good'
        ELSE 'Rejects'
    END
FROM [Validations] 
OUTER APPLY (

    SELECT 
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] ) ), 0 ) AS [Report_Year_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] ) ), 0 ) AS [COS_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] ) ), 0 ) AS [Franchise_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] ) ), 0 ) AS [Product_Count]

) AS [ValidationCounts];

-- Return validation sql statements --

SELECT
    @ReportYearCmd AS ReportYearCmd,
    @CosCountCmd AS CosCountCmd,
    @FranchiseCountCmd AS FranchiseCountCmd,
    @ProductCountCmd AS ProductCountCmd;

-- Insert actual SQL statament results into Rejects Table --

IF(OBJECT_ID('tempdb..#TEMP') IS NOT NULL) 
BEGIN DROP TABLE #TEMP END
SELECT
    ReportYearCmd, CosCountCmd, FranchiseCountCmd, ProductCountCmd
INTO #TEMP
FROM (
    SELECT 
    @ReportYearCmd AS ReportYearCmd,
    @CosCountCmd AS CosCountCmd,
    @FranchiseCountCmd AS FranchiseCountCmd,
    @ProductCountCmd AS ProductCountCmd
    ) AS [RejectsTable]

This only returns the validation statements from the first select statement, but I don't see a RejectTable showing the actual rejects from those SQL statements...

Currently, when I execute this SP I get something like:

ReportYearCmd CosCountCmd FranchiseCountCmd  ProductCountCmd

NULL          SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] )        NULL               SELECT DISTINCT [Style Code] AS [Product - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] )

I'd like to return the actual result, i.e. if the Cmd is NOT NULL, as in the example above, return CosCountCmd and ProductCountCmd data:

COS - Country Code     Product - Style Code 
reject1         Rejectxy
reject2         Reject1234
NULL            Reject567

I followed some of the examples here but none of them seem to work for me...

Temp tables go out of scope at the end of the SP... so it will gone by the time you return. — Dale K, Sep 14 '20 at 21:43
@DaleK oh...but i have the GO after it...shouldnt that make a difference? where am i supposed to place it then — Cataster, Sep 14 '20 at 21:50
GO is a client command to separate statements. So does nothing. — Dale K, Sep 14 '20 at 21:50
You either have to create a *real* table for returning the results via, or create your temp table in the calling context because it is available for any called SPs. — Dale K, Sep 14 '20 at 21:51
@DaleK, i see. can you elaborate on this? `create your temp table in the calling context` — Cataster, Sep 14 '20 at 21:55
@DaleK oh i see... i guess its easier to just create the table itself then — Cataster, Sep 14 '20 at 22:01
@DaleK one more question. how can i unload the actual results into the table of the validation sql statements that are selected? — Cataster, Sep 14 '20 at 22:02
Depends on your use case, I use both ways depending on my situation. — Dale K, Sep 14 '20 at 22:02
"how can i unload the actual results into the table of the validation sql statements that are selected" - sorry not sure what you mean by this. Might need another question. — Dale K, Sep 14 '20 at 22:03
@DaleK updated my pos at the endt with what i am currently getting and what i am looking for — Cataster, Sep 14 '20 at 22:04
So you want to execute the SQL statements you have built if they fail validation? — Dale K, Sep 14 '20 at 22:06
@DaleK Yep! so for example, this is what the `SELECT @CosCountCmd AS CosCountCmd`, statement currently returns: `SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] )`. but i need the actual execution of this select — Cataster, Sep 14 '20 at 22:07
OK, so that requires Dynamic SQL - which I suggest you check out some tutorials for. And then another question. This question was only about why the temp table wasn't showing. — Dale K, Sep 14 '20 at 22:07
@DaleK ok ill post another question. should i delete this question then? or would you like to submit an answer and i'll mark it? — Cataster, Sep 14 '20 at 22:08
I'll answer. Another option to consider, depending on how it suits your system, is writing a CLR function to evaluate your validation rules. I've done that before, and then have more control over it. — Dale K, Sep 14 '20 at 22:09
@DaleK interesting! could you tell me more about it in another question? actually, i think this question here should already be suitable for this https://stackoverflow.com/questions/63890925/export-stored-procedure-results-into-a-staging-table — Cataster, Sep 14 '20 at 22:12
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/221484/discussion-between-dale-k-and-cataster). — Dale K, Sep 14 '20 at 22:15

Dale K · Accepted Answer · 2020-09-14T22:21:59.527

2

Local temporary tables are visible only in the current session. And as such:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished.

In fact a global temp table (defined using a double number sign e.g. ##temp) will probably do what you want:

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them.

Otherwise you could define a temp table in the calling scope i.e. before you call your SP or use a regular table.

edited Sep 14 '20 at 22:21

answered Sep 14 '20 at 22:11

Dale K

25,246
15
42
71

thank you. i found this answer here too, and apparently an extra # makes a huge difference? https://stackoverflow.com/a/9893172/8397835 so ##TEMP means its more of a permanent table? – Cataster Sep 14 '20 at 22:16
1

Thats a global temp table - all explained in the reference I linked to. In fact that might do what you need. – Dale K Sep 14 '20 at 22:20

Why is the #TEMP Table not showing up?

1 Answers1