Invalid Object name ##Temp Error when creating a global temporary table

Question

I have data in a couple tables that I want to load into a temporary table in SQL Server.

I am creating a stored procedure that gets the data from the tables. Then I create the global temporary table and insert the results from the stored procedure into it.

However when trying to select the data from the temporary table I get this error 'Invalid object name ##Temp'

Why am I getting this error if it is a global temporary table?

DROP PROCEDURE usp_GetEmp
GO
CREATE PROCEDURE usp_GetEmp
AS
BEGIN
    SELECT table1.id AS Id
      , table2.data AS table2_value
  FROM table1 INNER JOIN table2
  ON table1.id = table2.table1_id
  WHERE table2.data = 1
END

  CREATE TABLE ##Temp
  ( Id Int
  , Value varchar(50))

  INSERT INTO ##Temp
  EXEC usp_GetEmp
  GO

  SELECT *
  FROM ##Temp

Global temp tables are a sign that something is a bit off with your design. They are fraught with concurrency and scope issues. Do you have a GO in there. Otherwise your create table and such will all be inside the procedure. — Sean Lange
Wow that was the issue. I didn't originally have it has a global temp table but I assumed that error was because it wasn't so I changed it to a global scope. It is no longer global and works. Thanks! — Deric Plummer
You do realize that your Stored Procedure includes the Create Table and Insert Into statement because of where your GO statement is. Credit to @Fabiano below. I didn't see his answer until now. — SS_DBA

Fabiano Carvalho Fabiano Carvalho · Accepted Answer · 2017-06-07T16:14:10

Try it's

DROP PROCEDURE USP_GETEMP
GO
CREATE PROCEDURE USP_GETEMP
AS
BEGIN
SELECT 
    TABLE1.ID AS ID
    ,TABLE2.DATA AS TABLE2_VALUE
FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.ID = TABLE2.TABLE1_ID
WHERE TABLE2.DATA = 1
END
GO

IF OBJECT_ID('TEMPDB.DBO.##TEMP') IS NOT NULL
DROP TABLE ##TEMP
GO
CREATE TABLE ##TEMP
( ID INT
, VALUE VARCHAR(50))

INSERT INTO ##TEMP
EXEC USP_GETEMP
GO

SELECT *
FROM ##TEMP

Invalid Object name ##Temp Error when creating a global temporary table

1 Answers