error handling - 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)

error handling

Author

Topic

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2005-06-29 : 02:14:53

i'm trying to capture during an insert, a PK or FK violation and send that back from a stored proc as an output variable:

create proc proc1 (name varchar(10), empID int, error_code varchar(255) OUTPUT

blah, blah
insert record but violates PK (same empID is being inserted)
***this would normally cause a PK violation and the proc would error out..***
what I want to do is if the insert fails due to a PK or FK error, instead of the proc failing horribly, I want to capture the error and return it as error_code

suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-29 : 02:30:13

After your insert statement, use this


Declare @ErrNo int
Declare @ErrMessage varchar(500)
select @ErrNo=@@Error
Select @ErrMessage = Description from master..sysmessages where error=@errNo
Select @ErrMessage

Now @ErrMessage will have error message and return it

Madhivanan

Failing to plan is Planning to fail

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-06-29 : 04:04:36

It all depends on the kind of error (severity). If the error is Fatal then you can't handle it.
Refer to this post:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50568[/url]

Check this article for error handling:
[url]http://www.sqlteam.com/item.asp?ItemID=2463[/url]

You can't teach an old mouse new clicks.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-29 : 12:51:53

DBASlut,

Don't know your background, but SQL Server handles different types of errors, well, differently.

In the case of the PK/FK constraints, it will raise out and you won't have any control, except that the calling code will know.

BUT!

YOu can premptively check before the error occurs. It entails making the same call twice though, so it's a trade off.

Why though, can't you capture the raised error in the calling code?

For example, for a PK violation

IF EXISTS (SELECT * FROM myTable99 WHERE key = @key)
BEGIN
SET @Error = -1
RETURN
END
ELSE
INSERT INTO myTable99(Collist...)SELECT Collist

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2005-07-05 : 13:27:12

Thank you all for the info. Looks like I'll have to check prior to doing the insert (pre) instead of blindly inserting and catching the error (post)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources