Error Handling not working, any expert pls...

[peac3]

Hi guys,

I have stored procedure as below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[proc_adj] 
AS
SET NOCOUNT ON    

DECLARE @proc_name            sysname,
            @error                  INT

-- initialise error/debug variables
SELECT      @proc_name = OBJECT_NAME( @@PROCID ),
            @error = 0



select test from test2
IF @@error <> 0 GOTO ADJ_MAP_ERROR



ADJ_MAP_ERROR:
    RAISERROR ('%s: Adjustment mapping failed (SQLerror: %d)', 16, 1, @proc_name, @error)
    RETURN (@error)


PRINT convert(varchar, getdate()) + ' FINISHED'

I am trying to test my error handling and ran stored proc above and I got error as below :

Msg 208, Level 16, State 1, Procedure proc_adj, Line 18
Invalid object name 'test2'.

I expected the error would say something "Adjustment mapping failed", any clue why it's not working?

Thanks guys.

 

[djj55]

Questions:
1) What version of SQL are you using
2) Does the table test2 exist, if so does it exist in the database where you are running the code?


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[peac3]

Hi djj55

The sql is MSSQL 2005
and the table2 does NOT exist as I just wanna test my error handling so I expect it goes to "ADJ_MAP_ERROR" but it does not.

Any idea why?

Thanks man,

 

[danvlas]

If I were you I would use Try...Catch to detect and manage errors/exceptions.
Try a 'division by zero' error.

alter PROCEDURE [dbo].[proc_adj] 
AS
SET NOCOUNT ON    
declare @errortext varchar(max)
DECLARE @proc_name            sysname,
            @error                  INT

begin try
	select 1/0
    --select test from test2
end try

begin catch
    select @error=error_number()
    select @errortext='%s: Adjustment mapping failed(SQLerror: %d)' + error_message() + ', procedure ' + error_procedure()
    raiserror(@errortext,16,1)
end catch

PRINT convert(varchar, getdate()) + ' FINISHED'
RETURN (@error)

Daniel Vlas
Systems Consultant

 

[SajidAttar]

this might be because of the error severety level is 16, that means the error can be corrected by developer and SQL server terminates the program here. EVEN TRY.. CATCH block will behave same.

Try raising error of low severity.

execute below code and study the procedure execution with error handling

CREATE PROC ErrSev16_one
AS
BEGIN
	DECLARE @ERR INT
		SELECT * FROM SDSCallDetailAA
		SELECT @ERR = @@ERROR
		IF @ERR <> 0
		BEGIN
			PRINT 'ERROR OCCURED A'
		END
END

go

CREATE PROC ErrSev16_TWO
AS
BEGIN
	BEGIN TRY
			SELECT * FROM SDSCallDetailAA
	END TRY
	BEGIN CATCH
		PRINT 'ERROR OCCURED A'
	END CATCH
	
END

GO

CREATE PROC ErrSev16_THREE
AS
BEGIN
	BEGIN TRY
		IF OBJECT_ID('SDSCallDetailAA') IS NOT NULL
		BEGIN
			SELECT * FROM SDSCallDetailAA
		END	
		ELSE
		BEGIN
			RAISERROR('CUSTOM ERROR WITH LOW SEVERITY',11,1)
		END
	END TRY
	BEGIN CATCH
		PRINT 'ERROR OCCURED A'
	END CATCH
	
END
go

CREATE PROC ErrSev16
AS
BEGIN
	DECLARE @ERR INT
	IF OBJECT_ID('SDSCallDetailAA') IS NOT NULL
	BEGIN
		SELECT * FROM SDSCallDetailAA
		SELECT @ERR = @@ERROR
		IF @ERR <> 0
		BEGIN
			PRINT 'ERROR OCCURED A'
		END
	END
	ELSE
	BEGIN
		RAISERROR('OBJECT DOES NOT EXISTS',11,1)
		SELECT @ERR = @@ERROR
		IF @ERR <> 0
		BEGIN
			PRINT 'ERROR OCCURED B'
		END
	END
		
	
END

Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD