• Aleksandr Furman (8/27/2008)


    skyline666 (8/27/2008)


    Yes thats a good point, temporary tables only exist for the life of the stored procedure (when prefixed with one #), I temporarily forgot about that ;).

    That's not exactly true. Temp table exists for the life of the connection. Try to rerun same code in the same query window (same connection id) and you'll get an error saying that table already exists. It's a good practice to always clean up after yourself.

    Hi Aleksandr,

    Local temporary tables are dropped when the stored procedure completes. The behaviour you describe applies not to stored procedures (unless you use global temp tables instead of local temp tables).

    Here is an example that creates a temp table within the stored procedure dbo.Test and tries to select from the table after the procedure completed:

    CREATE PROCEDURE dbo.Test

    AS

    CREATE TABLE #Test (ColA int NOT NULL);

    GO

    EXEC dbo.Test;

    GO

    SELECT * FROM #Test;

    Best Regards,

    Chris Büttner