Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Post Unanswered: tempdb table in Stored procedure

    We have a stored procedure which has following code

    create procedure test_proc

    if exists (select 1 from tempdb..sysobjects where type='U' and name='test_data')
    insert into tempdb..test_data values (getdate())

    Tempdb table was obviously created prior to creating proc. As long as tempdb table exists the code works fine. Expectation is that if tempdb table does not exist then the above insert code should not execute. Server was recycled so temp table is gone and sp throws error on insert - why is th at? Without recycling server only if tempdb table was dropped insert stmt is not executed. what is difference in 2 cases.

  2. #2
    Join Date
    Mar 2008
    Tempdb database is the temporary database that gets recreated everytime the Server is recycled.
    So Whenever u recycle the server, everything in tempdb is gone..... so your temp table too.
    So to get your tables in tempdb everytime after server recycle, you need to create the table in model database. Model database is the skeleton database, which is used as a prototype to create any new database. So you must be aware after creating the table in Model, the same table will be automatically created in any database which you create after making above said modification in model database.

  3. #3
    Join Date
    Jan 2004


    Question is not about why the tempdb table disappeared or what is the solution. It is about why behaviour is different in the following two cases.

    1. Manually drop tempdb table. Execute stored procedure. It correctly identifies that tempdb table does not exists (i.e. if exists evaluates to false) and skips the insert statement

    2. Instead of manually dropping tempdb table server is recycled thus tempdb table is gone
    Now execute the stored proc. In this case it does not identify that table does not exist and tries to execute insert statement. i.e. if exists as mentioned in the stored proc code is ignored.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts