SQL question: How to get a true/false value for whether or not a table exists?

[Vexorg]

I am trying to write a stored procedure in T-SQL that inserts a record into a table, then is supposed to return the primary key (EmpID) for that record as an output parameter. How I'm trying to do that is by using a trigger on the table's insert command that retreives the EmpID parameter out of the inserted table, and uses a SELECT into to put it into another table. Based on what I have, the following will work and return the appropriate value:

drop table tblTemp
declare @empidout int
insert into Employee (Title, FirstName, MiddleName, LastName, 
BirthDate, HireDate, Salary) values ('Doctor of German Repairs', 'Otto', null,
'Partz', '02-23-75', '01-01-2003', 65000)
set @empidout = (select EmpID from tblTemp)
select @empidout

The hangup is that in the stored procedure the query normally runs in, I need a way to tell it to drop the table if it exists. Is there some way I could return a 0 or 1 value to determine if the table exists or not to put in this?

if @tblExists = '0'
drop table tblTemp

 

[Cardboard Hammer]

Check the sysobjects table in the same database.

This should do:

IF (SELECT count(*) FROM sysobjects WHERE name = TableName AND type = 'U ') = 1

You'd also need to check the uid, if the same table name might be created for multiple users.

 

[Cardboard Hammer]

On the other hand, if EmpID is an auto-increment field, do the INSERT followed by a SELECT MAX(EmpID) FROM Employee. Put the two statements inside a serializable transaction to ensure that nothing can sneak between them and cause MAX(EmpID) to increase.

Or you can search for the record having all the values just inserted (presuming that the entire combination (or at least a subset) must be unique).

 

[Cardboard Hammer]

Also, there's a difference between an output parameter and a single field, single row result set (which is what your code actually has).

EDIT: If a single field, single row result set is suitable, you can just SELECT the EmpId from the "inserted" table (special table available to triggers that, along with the "deleted" table, allows one to see within the trigger the change(s) that fired the trigger) within the trigger and that result set will be returned to the caller of the stored procedure.

 

[Vexorg]

OK, I'll go with the SELECT MAX, as that seems to work. Given the fact that the EmpID is the only unique field in the table (and the data given contains quite a few duplicates.

As for the trigger I was trying to use, upon further investigation it appears that the problem I was having was due to the fact that I was logged on as a different user from the connection string in the app calling it than I was in Query Analyzer.


Thanks for the tip.