Cannot access temp table in dynamic TSQL

Question

I am creating a temporary table in some dynamic SQL. But when I call it it throws an "Invalid object name '#Settlement_Data_Grouped'" error.

I am assuming it's because dynamic SQL uses it's own seperate instance? So that it is dropped and not available to the outside SQL? It works when I use ##Settlement_Data_Grouped or create a table. But this doesn't help when multiple people are calling the sproc.

I'm thinking I could check if the table exists but that would mean I would have to delete contents and different users may require different outputs which would mean it would not work.

So does anyone have a solution/suggestion I can use where multiple people can be calling the same sproc?

Among the "Related" links on this page are [this](https://stackoverflow.com/questions/2917728/t-sql-dynamic-sql-and-temp-tables?rq=1) and [this](https://stackoverflow.com/questions/40521021/insert-data-into-temporary-table-from-dynamic-query-table-output?rq=1). Do they help? Aside: At first glance it isn't clear that you need to be using dynamic SQL. Repeating code is not always a terrible thing. — HABO, Jul 19 '17 at 00:13

score 1 · Accepted Answer · answered Jul 19 '17 at 00:44

IMHO you do not need the Dynamic SQL. You can change your code like something below and that will give you same result as you are trying to accomplish. Please check for any syntax error if any. I would have provided you full query but in your question you have screenshot instead of the code. So here it goes.

If you want to use Temp Tables:

SELECT
......
INTO #Settlement_Data_Grouped
FROM #Settlement_Data
WHERE (Payment_Date < Settlement_Date AND @outputType = 1) ---This will be true when you have @outputType = 1 
    OR @outputType = 0 ---This will be true when you have @outputType = 0
GROUP BY Part_No
    ,NAME
    ,Order_No
    ,Invoice_No

-------------
SELECT
......
FROM #Settlement_Data_Grouped

If you want to use CTE:

WITH Settlement_Data_Grouped
AS (
SELECT
......
FROM #Settlement_Data
WHERE (Payment_Date < Settlement_Date AND @outputType = 1) ---This will be true when you have @outputType = 1 
    OR @outputType = 0 ---This will be ture when you have @outputType = 0
GROUP BY Part_No
    ,NAME
    ,Order_No
    ,Invoice_No
)

SELECT
......
FROM Settlement_Data_Grouped

I actually ended up doing this and now just read your comment. Sometimes the answer is just so damn obvious but you don't see the wood for the trees. — Danrex, Jul 20 '17 at 02:32

score 1 · Answer 2 · answered Jul 19 '17 at 11:16

The problem is that the temp table only exists within the scope of the dynamic SQL execution context. The way around it would be to create the temp table outside of the dynamic SQL and then insert into it:

CREATE TABLE #Settlement_Data_Grouped (PartNo varchar(50) ......)

INSERT INTO #Settlement_Data_Grouped
EXEC(@selectSQL)

Cannot access temp table in dynamic TSQL

2 Answers2