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.
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.
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.