Exception Handling T-SQL (SQL 2000)

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

General SQL Server Forums

New to SQL Server Programming

Exception Handling T-SQL (SQL 2000)

Author

Topic

russell101
Starting Member

3 Posts

Posted - 2008-02-04 : 21:09:41

Hi,

I'm trying to do exception handling in a stored procedure, such as IF (@@ERROR <> 0) execute and insert on to an error log table. I have set the volatile @@ERROR global to a variable, but the proc is still throwing a primary key exception; hence, it's not being trapped. My question is why isn't my exception being handled?

Here's my code, your help is much obliged. (my error proc is below the caller)

CREATE PROCEDURE [dbo].[INSERT_STATS_NO_GROUP_PROC]
@stat_type_id int,
@stat_delimited_file_id smallint,
@time_interval datetime,
@call_volume int,
@Err int OUTPUT
AS
SET @Err = 0
DECLARE @Error int
BEGIN TRANSACTION
INSERT INTO DAILY_SUMMARY_STATISTICS
(
stat_type_id,
stat_delimited_file_id,
ssda_customer_id,
time_interval,
call_volume
)
VALUES
(
@stat_type_id,
@stat_delimited_file_id,
NULL,
@time_interval,
@call_volume
)

SET @Error = @@ERROR
IF(@Error <> 0)
BEGIN
GOTO abort
END
COMMIT TRANSACTION

abort:
ROLLBACK TRANSACTION
EXECUTE SET_ERROR_TO_LOG_PROC @SQLErrorID = @Error,@ObjectName = 'INSERT_STATS_NO_GROUP_PROC'
SET @Err = @Error

/*ERROR PROC*/

CREATE PROCEDURE [dbo].[SET_ERROR_TO_LOG_PROC]
@SQLErrorID int,
@ObjectName varchar(50)
AS
DECLARE @SQLErrDescription varchar(200)
SELECT @SQLErrDescription = description FROM master..sysmessages WHERE error = @SQLErrorID
BEGIN TRANSACTION
INSERT INTO [dbo].[OSAUTOREPORTS_ERROR_LOG]
(
error_id,
error_description,
object_name
)
VALUES
(
@SQLErrorID,
@SQLErrDescription,
@ObjectName
)
COMMIT TRANSACTION

Thanks Russ

CShaw
Yak Posting Veteran

65 Posts

Posted - 2008-02-05 : 00:30:42

What does the OSAUTOREPORTS_ERROR_LOG table look like?

Chris Shaw
www.SQLonCall.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 07:54:57

www.sommarskog.se/error-handling-I.html
www.sommarskog.se/error-handling-II.html

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-05 : 12:51:26

It sounds like there might be a disconnect on how error handling works...?

When an error occurs, you can check to see that it happened, like you are doing. And then act accordingly, but I don't think you can actually TRAP the error like a TRY-CATCH can. Maybe if you use SET_XACT_ABORT, but I've seen this not work in the past so it is suspect to me. In most cases, SQL will error and happily continue on its merry way.

Are you trying to stop SQL from raising the error that you are logging?

For illustration purposes, here is a sample that generates an error and continues on and generates another error. Both error are raised.

DECLARE @Error INT

DECLARE @Yak TABLE (ID INT, Val INT NOT NULL)

INSERT @Yak
SELECT 1, 1
UNION ALL SELECT 1, 2

SET @Error = @@ERROR

IF @Error <> 0
BEGIN
	PRINT 'ERROR'
END
ELSE
	PRINT 'No Error'

INSERT @Yak
SELECT 1, NULL

SET @Error = @@ERROR

IF @Error <> 0
BEGIN
	PRINT 'ERROR'
END
ELSE
	PRINT 'No Error'

INSERT @Yak
SELECT 1, 3

SET @Error = @@ERROR

IF @Error <> 0
BEGIN 
	PRINT 'ERROR'
END
ELSE
	PRINT 'No Error'

INSERT @Yak
SELECT 1, NULL

SET @Error = @@ERROR

IF @Error <> 0
BEGIN
	PRINT 'ERROR'
END
ELSE
	PRINT 'No Error'

russell101
Starting Member

3 Posts

Posted - 2008-02-06 : 19:23:52

Hi Lamprey,

You were correct in your assumption. I was trying to 'catch' the error. What I was simply trying to do was insert any error that occurs in a SQL_Error log. But when I test it in the Query Analyzer, SQL Server raises the error and jumps out of the scripts. Is there anyway around this?

Thanks Russ

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-07 : 12:41:46

I'm not sure I'm following, unless you have a GOTO thr script should execute in sequence. One thing I noticed about your script is after the COMMIT TRANSACTION you need another GOTO to jump over the error handling part (unless you want that to exeute every time). For example:

IF(@Error <> 0)
BEGIN 
GOTO abort
END
COMMIT TRANSACTION
GOTO EndProc

abort:
ROLLBACK TRANSACTION
EXECUTE SET_ERROR_TO_LOG_PROC @SQLErrorID = @Error,@ObjectName = 'INSERT_STATS_NO_GROUP_PROC'
SET @Err = @Error

EndProc:

If you can provide a little more info I'm sure we can help you out. I think your pretty close already.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources