Drop Table if exists 2

[jgurgen]

In the beginning of my proc i want to drop the temp table if it exists. in mysql it would be...
DROP TABLE IF EXISTS Temp_Table

how do i do it in MSSQL?

 

[ptheriault]

What kind of temp table is it? Is it static or a #temp table?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jgurgen]

right now i have it as
CREATE TABLE TEMP_TABLE

 

[onpnt]

That isn't a temp table for one. SQL Server has the same related functional If Exists keyword and functionality

http://www.sqlteam.com/item.asp?ItemID=14820

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[ptheriault]

Use this to drop a static table.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEMP_TABLE]') 
and OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE [dbo].TEMP_TABLE
GO

But you may want to use SQL Server temp tables. Look up temp tables in BOL.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jgurgen]

would i be better in doing

CREATE TABLE #Temp_Table

then would i have to check if the table exists in case the procedure crashed?

 

[SQLSister]

Inthe first place you do not want to create a temp table that way, it will create an actual table in your database.
This is a very inefficent way to do business and you can run into problems if multiple people are accessing the data.

YOu want to create a true temp table or a table variable.

CREATE TABLE #TEMP_TABLE

This will create a table in teh temp db that will go out of scope and drop as soon as the proc is done running. No one other than the user running the proc can see this table and two users can run the same proc simulataneously with different input values and each get their own table.

In this case you generally do not need to check for existence because if you haven't created it yet in the proc, it doesn't exist.

To check for existence of a table

IF  EXISTS (SELECT * FROM test..sysobjects WHERE type = 'U' AND name = 'Mytable')
Begin
insert some code
End
[\code}





Questions about posting. See faq183-874

 

[onpnt]

If you use # is will be destroyed upon that sessions ending. If you use ## then it is global and it would be best to check for it.

Take Paul's suggestion on looking in BOL for temp tables.

I would not create static tables to represent "temp" tables. It is not a good idea

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[jgurgen]

thanks for all the help works great