TSQL exception handeling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TSQL exception handeling

Author

Topic

sherazqm
Starting Member

1 Post

Posted - 2003-09-10 : 03:34:13

i want exception handeling in stored procedure. i tried the @error but it did not meet my requirements.
a simple delete query in SP, when find a child record against the record which is being deleted the foreign key constraint error occur. i dont want to handel it on the front end in try catch block. i want to handel it at back end.
Thanks

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-09-10 : 04:46:08

I think there is an example on what you want in BOL
Take a look at @@error -> Using @@error

Cheers,
Frank

dsdeming

479 Posts

Posted - 2003-09-10 : 08:42:27

Frank is right about the error trapping.

One other thing to keep in mind when writing inserts and deletes is that to prevent errors like the one you describe, always insert from the top down and delete from the bottom up; that is, insert into the parent table and then the child table but delete from the child and then the parent.

Dennis

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-10 : 13:06:58

quote:
Originally posted by sherazqm

i want exception handeling in stored procedure. i tried the @error but it did not meet my requirements.
a simple delete query in SP, when find a child record against the record which is being deleted the foreign key constraint error occur. i dont want to handel it on the front end in try catch block. i want to handel it at back end.
Thanks

OK, but what do you want to do in the backend if it happens? Nothing?

Usually I send a message to the GUI indicating that there was a problem.

But like Dennis said, I think it's more a coding issue. Shouldn't you check to see if children exists before you attempt the delete?

And if there are children. Do you wabt to delete them? Or leave them

In any event you need a plan on what to do.

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-10 : 13:41:21

Check out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26231&SearchTerms=Error,Handling

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources