Creating Temptable in Stored Procedure

[mydisney]

I have a stored procedure that creates a temp table (see code below).

1. How can I drop the table if it exists?
2. Also I have define a field called "job_date" and I get an error if I specify 'date' as type (so I changes it to 'int')?

create table #jobtemptable
(
job_date int,
job_number char(15),
job_phase char(15),
asphalt_delivered int,
job_received int
)
GO

 

[jbenson001]

1.)
if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U') and
o.id = object_id( N'tempdb..'+'#jobtemptable' )

drop #jobtemptable

2.) There is no date type in sql server, it is datetime.

 

[mydisney]

I get a syntax error: Incorrect syntax near keyword "drop" ...

CREATE PROCEDURE sp_CreateJobTempTable
as

if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U') and
o.id = object_id(N'tempdb..'+'#jobtemptable' )
drop #jobtemptable

create table #jobtemptable
(
job_date datetime,
job_number char(15),
job_phase char(15),
asphalt_delivered int,
job_received int
)
GO

 

[gmmastros]

There's a missing 'close parenthesis'. Can you find it?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

I tried to place the missing "parenthesis" after the '#jobtemplate' but still get syntax error.

 

[gmmastros]

There is a parenthesis missing and the drop temp table line isn't right either. Try this...

if exists (
    select  *
    from
        tempdb.dbo.sysobjects o
    where
        o.xtype in ('U')    and
        o.id = object_id( N'tempdb..'+'#jobtemptable' )
      [!])[/!]

drop [!]table[/!] #jobtemptable

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

It worked... thank you.....

 

[gmmastros]

You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

thanks George, I typed to quickly