Temporary table trouble in SQL Server

I have 2 store procedure :

The first one to create #TempTable

CREATE PROCEDURE CreateTempTable
AS
BEGIN
    IF OBJECT_I开发者_开发百科D('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 ?

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)

I guess the problem here is that you are creating a local temporary table, that cannot be accessed outside CreateTempTable. You should create a global temporary table, by using ## instead of #.

Edit Yep, that's it. Here is your fixed script:

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
GO

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

exec CreateTempTable
exec InsertData 1
go