Temporary table trouble in SQL Server

Question

I have 2 store procedure :

The first one to create #TempTable

CREATE PROCEDURE CreateTempTable
AS
BEGIN
    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    BEGIN
        DROP TABLE #TempTable;
    END 

    CREATE TABLE #TempTable(
        Value real NOT NULL         
END

The second one to insert data in my #TempTable

   CREATE PROCEDURE InsertData
         @Value real 
    AS
    BEGIN
      INSERT #TempTable (Value) VALUES @Value
    END

When I call these procedure I have an error :

exec CreateTempTable
exec InsertData" 1
go

Name '#TempTable' not valid in InsertData

Can you help me ?

Answer 1

A temp table created inside a sproc is automatically dropped after the sproc ends.

You have a few choices:

• Create the temp table outside of the sproc as a standalone query. Then it will be dropped after the connection closes
• Create a sproc that first creates the temp table and then calls the other sprocs
• Use a global temp table (careful - concurrency issues can crop up with this)