A curio with Object_ID

Question

Post reply

A curio with Object_ID

Simon-220850

SSCrazy

Points: 2789
More actions
July 1, 2005 at 7:25 am

#164772

Hi I have a script which does the following
IF Object_Id('#guk_temp_current_appointment_card_roles') is Not Null
  BEGIN
   DROP TABLE #guk_temp_current_appointment_card_roles
  END
GO
If Object_Id('idx_#guk_temp_current_appointment_card_roles_registration_no') is Not Null
  BEGIN
   DROP INDEX #guk_temp_current_appointment_card_roles.idx_#guk_temp_current_appointment_card_roles_registration_no
  END
GO

If Object_Id('idx_#guk_temp_current_appointment_card_roles_expiresdate') is Not Null
  BEGIN
   DROP INDEX #guk_temp_current_appointment_card_roles.idx_#guk_temp_current_appointment_card_roles_expiresdate
  END
GO
CREATE TABLE #guk_temp_current_appointment_card_roles(
  registration_no INTEGER,
  title CHAR(15),
  first_initial CHAR(1),
  first_name CHAR(20),
  surname CHAR(25),
  address1 CHAR(40),
  address2 CHAR(40),
  address3 CHAR(40),
  address4 CHAR(40),
  address5 CHAR(40),
  address6 CHAR(40),
  post_code CHAR(10),
  bulk_title CHAR(15),
  bulk_first_name CHAR(20),
  bulk_surname CHAR(25),
  bulk_address1 CHAR(40),
  bulk_address2 CHAR(40),
  bulk_address3 CHAR(40),
  bulk_address4 CHAR(40),
  bulk_address5 CHAR(40),
  bulk_address6 CHAR(40),
  bulk_post_code CHAR(10),
  guiding_county CHAR(50),
  role VARCHAR(150),
  expiresdate DATETIME,
  instruction_code CHAR(1),
  letter_code CHAR(1),
  role_no INTEGER
  CONSTRAINT [pk_#guk_temp_current_appointment_card_roles] PRIMARY KEY CLUSTERED (role_no)
   &nbsp ON [PRIMARY]

CREATE NONCLUSTERED INDEX idx_#guk_temp_current_appointment_card_roles_registration_no
  ON #guk_temp_current_appointment_card_roles (registration_no)

CREATE NONCLUSTERED INDEX idx_#guk_temp_current_appointment_card_roles_expiresdate
  ON #guk_temp_current_appointment_card_roles (expiresdate)
HOWEVER when I run this script for the 2nd time, I get an error
There is already an object named '#guk_temp_current_appointment_card_roles' in the database.
The line
IF Object_Id('#guk_temp_current_appointment_card_roles') is Not Null
would appear to have evaluated to false and not continued to drop the table. However if I highlight the
DROP TABLE #guk_temp_current_appointment_card_roles
command on its own and execute it, it succeeds
firstly why could this be happening and secondly, is there a better way of determining if a table or index exists
Thanks

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

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

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 1

change this IF Object_Id('#guk_temp_current_appointment_card_roles') is Not Null

to IF Object_Id('#guk_temp_current_appointment_card_roles') >0

hth

Mike

Simon-220850 SSCrazy Points: 2789 More actions · Answer 2

I neglected to say that I am running this logged on as sa, and have also tried the code without the go batch seperators to no avail.

Simon-220850 SSCrazy Points: 2789 More actions · Answer 3

Hi Michael,

I've tried IF Object_Id('#guk_temp_current_appointment_card_roles') >0

unfortunately this also gives the same error

I've even tried hedging my bets with

IF coalesce(Object_Id('#guk_temp_current_appointment_card_roles'),0) >0

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

Since the temp table is not created in your user database (and also is not created with the name you use for it, in reality it has a suffix also) OBJECT_ID will always return NULL for that table.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Either use your if object_id is not null in your tempdb database (which is where your temp table is created) - or just drop it!!!!

**ASCII stupid question, get a stupid ANSI !!!**

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 6

Sorry asleep at the wheel this morning change the statement to. Is not null works as well as >0

hth Mike

IF Object_Id('tempdb..#guk_temp_current_appointment_card_roles') is not null

DROP TABLE #guk_temp_current_appointment_card_roles

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 7

Just an aside one of the things I love about C is that when you want to delete file you just Kill it. So much more satisfying.

Mike

Simon-220850 SSCrazy Points: 2789 More actions · Answer 8

"Sorry asleep at the wheel this morning change the statement to"

Oh God me too, Sound of hand slapping forehead.

I was originally using normal tables and then switched to temp tables ...and it failed to register somwhere in the deep dark recesse of my brain 😉

Thanks Guys