error handling how do I.....

Question

error handling how do I.....

william crawley

SSC Veteran

Points: 255
More actions
February 21, 2007 at 12:49 am

#92972

Hi All,
I Have a stored procedure in sql server 2000.
At the start I drop some temp tables, however if the tables do not exist an exception is thrown. If they dont exist I dont care. How can I turn off exception handling at the start of my drop table section and then turn in back on again after?

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

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

jordanac Hall of Fame Points: 3653 More actions · Answer 1

Why not just use if exsists?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Employee]

Andy.

william crawley SSC Veteran Points: 255 More actions · Answer 2

Hi Andy,

Yes, I've resorted to using that. It's just that I have a number of tables and I thought it would be easier to just turn error handling off at the beginning and then back on again when I want it.

bledu SSChampion Points: 12335 More actions · Answer 3

why don't you use dynamic tables, that way you won't have to worry about them errors coming up.

e.g. from BOL 2005

USE AdventureWorks;

GO

DECLARE @MyTableVar table(

EmpID int NOT NULL,

OldVacationHours int,

NewVacationHours int,

ModifiedDate datetime);

UPDATE TOP (10) HumanResources.Employee

SET VacationHours = VacationHours * 1.25

OUTPUT INSERTED.EmployeeID,

DELETED.VacationHours,

INSERTED.VacationHours,

INSERTED.ModifiedDate

INTO @MyTableVar;

--Display the result set of the table variable.

SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate

FROM @MyTableVar;

GO

--Display the result set of the table.

--Note that ModifiedDate reflects the value generated by an

--AFTER UPDATE trigger.

SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate

FROM HumanResources.Employee;

GO

Everything you can imagine is real.

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 4

If they are temporary tables, why are you trying to drop them? They should go out of scope whenever the stored proc is completed.

My guess is that you tested the stored proc as a script in QA and found that the tables hang around when you run the script a second time in the same QA window (which is the same scope)

Simply remove the delete statements, or better yet, explicitly remove the tables at the END of your stored proc (much better programming practice).

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Ray Herring SSCertifiable Points: 5533 More actions · Answer 5

Hi William,

I agree with Bledu. I use table variables A LOT. Generally they are every thing you want a temp table to be and more.

There are a couple of things to consider.

Table varibles are "in memory" not in the database.
They are FAST as long as they are not "too large". I use them for results with upto several thousand rows of and about 100 columns. (in excel terms I think of it as 100,000 or so cells) When the table gets larger it is time to consider other options.
Their scope is the containing Batch so a "GO" kills them. So in Bledu's script @myTable will no longer exist after the 2nd GO about 7 lines from the end of the script.
There are some 'irritating' limitations that I run across very rarely.

Give them a try. I think you will find them very useful.

william crawley SSC Veteran Points: 255 More actions · Answer 6

Guy's the reason I'm using temp tables in this way and deleting them at the start of the script is that the script runs during an overnight process and then the data in the temp tables are used during the day by various other processes. My own personal feeling is that these shouldn't really be temp tables and that they should be permenent and at the start of the script just use a standard 'Delete from.....' statement.

However the powers that be for whatever reason want the script to run as temp tables using the Drop Table option.

bledu SSChampion Points: 12335 More actions · Answer 7

this is a critical piece of info that you should have highlighted earlier.

i think you should have a table that is not really a temp table but then have an additional field that shows something like a unique run identifier. when you are done using the data you then just delete from the table based on this identifier instead of dropping the table. [you might have to create an additional table to keep track of your run number]

I think it would help if you can post your script excluding any confidential info.

Everything you can imagine is real.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 8

The other processes are using the same connection? Or are you talking about global temporary tables?