Hello

How to check a table is existing or not?

I have a stored procedure, which will create a new table for saving
queried result from two different database table. Before creating a new
table, I want to check the table is generated or not. If the table is
exist, I want to drop the table. Otherwise, a new table will be
generated.

Here is my code in the stored procedure:
IF OBJECT_ID('dbo.procedure_name') IS NOT NULL
BEGIN
IF OBJECT_ID('dbo.procedure_name') IS NOT NULL
DROP PROCEDURE dbo.procedure_name
ELSE
....
END
go

IF EXISTS(select * from sysobjects where name="tablename")
DROP TABLE tablename
go

create proc procedure_name
as
create table temp_tablename
(
....
)
insert
(...)
....

There are no problem on generating and inserting data into new table.
However, there is an error on detecting and dropping the old table.

Does anyone give me some suggestions on my coding? What code I should
put and where should I put it?

Thanks for you help

Joe

You could break it into 3 procedures.

Procedure 1 checks for the existance of the table.
If the table is present, Procedure 1 calls procedure 2.
Procedure 2 drops the table.
Procedure 1 then calls procedure 3 which will create and manipulate the
table.