Problem using IF EXISTS in a stored procedure

Question

Problem using IF EXISTS in a stored procedure

Dave Conover

SSC-Addicted

Points: 442
More actions
July 22, 2010 at 12:56 pm

#387410

Hi,
I am having a problem using IF EXISTS in a stored procedure. Within an IF EXISTS construct, I check to see if a specific table exists. If so, then I want to drop it and then go on to build a new instance of the table. The problem I am having is that I can not get the IF EXISTS statement to find an existing table. The syntax is correct but somehow it is not pointing to the right database. The following code example which worked fine in SQL Server 2000 is not logically working in SQL Server 2008:
Code Example:
------------------------------------------------
CREATE PROCEDURE usp_proc_d_zone AS
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[d_zone]') AND type in (N'U'))
DROP TABLE [dbo].[d_zone]
GO
SELECT zoneID,
[description]
INTO d_zone
FROM AdvertisingDW.dbo.zone
---------------------------------------------------
How do I get the sys.objects reference to point to the database AdvertisingDW?
Thank you.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

why would you want to drop and recreate it? This will cause users running this proc to have elevated privileges on the database. Why not just truncate it?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dave Conover SSC-Addicted Points: 442 More actions · Answer 2

Thanks for the reply. I agree that TRUNCATE makes more sense than doing a drop and recreate. However, I still can not get the IF EXISTS construct to find the existing table within the stored procedure. How do I specify a given database in the SELECT clause of the IF EXISTS construct within the stored procedure?

Ben Teraberry SSChampion Points: 11845 More actions · Answer 3

Will this always be performed in the same database?

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Mike01 SSChampion Points: 11395 More actions · Answer 4

ok, if you don't want to use truncate, then the reason your code doesn't work is because of the Go. Try this

CREATE PROCEDURE usp_proc_d_zone AS

begin

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =

OBJECT_ID(N'[dbo].[d_zone]') AND type in (N'U'))

DROP TABLE [dbo].[d_zone]

SELECT zoneID,

[description]

INTO d_zone

FROM AdvertisingDW.dbo.zone

end

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ben Teraberry SSChampion Points: 11845 More actions · Answer 5

Looking at this more ... I think he's trying to run this from another database. I'm guessing this is all he's trying to do:

CREATE PROCEDURE usp_proc_d_zone AS

IF EXISTS (SELECT * FROM AdvertisingDW.sys.objects WHERE object_id =

OBJECT_ID(N'[AdvertisingDW].[dbo].[d_zone]') AND type in (N'U'))

DROP TABLE [AdvertisingDW].[dbo].[d_zone]

SELECT zoneID,

[description]

INTO AdvertisingDW.dbo.d_zone

FROM AdvertisingDW.dbo.zone

But I still think there are better options than dropping and recreating the table each time.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

chandrakala.shetty SSC Enthusiast Points: 166 More actions · Answer 6

Have the below option in the begining of the script, i.e before calling the SP. This defaults the the provided database name & you do not have to give the DB name as part of the SP.

USER <DatabaseName>

Ben Teraberry SSChampion Points: 11845 More actions · Answer 7

chandrakala.shetty (7/22/2010)
Have the below option in the begining of the script, i.e before calling the SP. This defaults the the provided database name & you do not have to give the DB name as part of the SP.
USER <DatabaseName>

It would be

USE databaseName GO

and that doesn't help if he wants his proc to reside in a database that's not the same as where the table is, which seems likely given the problems he's having.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

chandrakala.shetty SSC Enthusiast Points: 166 More actions · Answer 8

Thanks!

That was a typo.

Yes, that was an assumption. Since developers normally face this when thier default database setting is set to something other that the one they want to work on.

If using a tool like management studio. Just choose the correct database from the drop down.

Dave Conover SSC-Addicted Points: 442 More actions · Answer 9

Mike01 and BTeraberry,

Thank you for all your help. I have followed your suggestions in the stored procedure:

1) Removed the GO following the IF EXISTS construct

2) Explicitly referenced the database in the FROM and WHERE clauses

3) Modified the code to TRUNCATE and then repopulate through the SELECT statement

It works perfectly and is actually faster than doing a drop and re-create.

I really appreciate your expertise and willingness to help.

Thank you again.