Drp Table Error

[mydisney]

I'm trying to drop a table and get error. I've tried to use the 'If Exists..." but cannot get the syntax to work

Error:
Cannot drop the table 'JobTable', because it does not exist in the system catalog.

CREATE PROCEDURE sp_CreateJobTable
as
DROP TABLE JobTable
create table JobTable
(
job_date datetime,
job_number char(15),
job_phase char(15),
asphalt_delivered int,
job_received int
)
GO

 

[gmmastros]

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] sp_CreateJobTable
[COLOR=blue]as[/color]
[COLOR=blue]If[/color] Exists([COLOR=blue]Select[/color] * [COLOR=blue]From[/color] Information_Schema.Tables [COLOR=blue]Where[/color] Table_Type = [COLOR=red]'Base Table'[/color] And Table_Name = [COLOR=red]'JobTable'[/color])
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] JobTable
  [COLOR=blue]End[/color]

[COLOR=blue]create[/color] [COLOR=blue]table[/color] JobTable
(
job_date [COLOR=#FF00FF]datetime[/color],
job_number [COLOR=blue]char[/color](15),
job_phase [COLOR=blue]char[/color](15),
asphalt_delivered [COLOR=blue]int[/color],
job_received [COLOR=blue]int[/color]
)
[COLOR=blue]GO[/color]

-George

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

 

[mydisney]

Thank you, it worked...

 

[Crowley16]

or for a shorter solution, you can do:

if object_id('JobTable') is null
drop table JobTable

of course, do be careful that there isn't a view or udf or something called JobTable

--------------------
Procrastinate Now!

 

[gmmastros]

???

Crowley, you suggested alternative code that is potentially buggy because it is shorter??? Despite the rumors... shorter is not always better.

Also, there's a silly little keyword missing from your bad advice.

if object_id('JobTable') is [!]not[/!] null
drop table JobTable

-George

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

 

[Crowley16]

it wouldn't be a problem if there was a consistent table prefix naming scheme...

--------------------
Procrastinate Now!

 

[gmmastros]

That may be true, but I prefer to NOT rely on other developers following standards. Look at the quote in my signature.

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

Personally, this is the sort of thing that keeps me up at night. I'd rather not have to worry about these things, so, I think it's better to add a little extra code to safeguard against it.

-George

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

 

[ca8msm]

it wouldn't be a problem if there was a consistent table prefix naming scheme...

True, but not everyone uses prefixes. Also, why create a risk when the first solution safeguards you against it?



-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Crowley16]

well, actually, you cannot create 2 objects of any type with the same name.

therefore, if you did have a view of the same name, both solutions will cause problems.

--------------------
Procrastinate Now!

 

[ca8msm]

well, actually, you cannot create 2 objects of any type with the same name.

therefore, if you did have a view of the same name, both solutions will cause problems.

I think you are missing the point. Yes, you are correct that you can't have two objects with the same name, but look at your method vs the one posted by George:

Your Method
If a view named "JobTable" exists, your object_id method will return a value and therefore the "Drop Table JobTable" command will be issued. As "JobTable" is a view, not a table, you will get the error:

Cannot use DROP TABLE with 'JobTable' because 'JobTable' is a view. Use DROP VIEW.

George's Method
With this method, it specifically looks for an object with the type of "Base Table", so even if there is a View with this name, the "Drop Table JobTable" command will not be issued.

So, George's example is the better solution as it won't error or produce incorrect results, and it isn't missing the correct boolean logic for checking if the object actually exists or not.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]