Error handling problem

15 Dec 2005 3:40 PM

Jakob Lithner

I am running SQL 2000.
I have several procedures where I do general validation on entered values.
User procedures typically call these "checkprocedures" to verify all entered
values.

I thought I grasped the idea of error handling but must have done something
wrong.
When a wrong value is found I throw an exception in the checking procedure
and return an arbitrary return code other than 0, typically 1.

In the calling procedure I check the @@ERROR value and then take action
based on that. The problem is that excution does not stop after the exception
is thrown even if I explicitly end with a Return in the calling procedure.

What is wrong?
Why does not the execution in the main procedure stop?
Maybe it is overkill to use both exceptions and return values:
Should I skip the exception throwing and rely on the Return values?
Should I skip the Return values and rely on the exception throwing?

My idea was to avoid defining the validation and exception messages on many
places.

Code example enclosed below:

CREATE PROCEDURE dbo.CheckLanguage
    @LanguageKey char(5),
    @LanguageID int OUT
AS

SET @LanguageID = NULL

-- Check if given language exists
SELECT @LanguageID = LanguageID
FROM Language
WHERE LanguageKey = @LanguageKey

IF @LanguageID IS NULL
BEGIN
    RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
16, 1, @LanguageKey)
    RETURN 1
END
GO

Calling procedure contains this code:
......
-- Make sure the given language key is valid
EXEC CheckLanguage @LanguageKey, @LanguageID OUT
SET @ErrorNumber = @@ERROR
IF @ErrorNumber <> 0
BEGIN
    SET @Response = 'Error_LanguageKey'
    RETURN 0
END
.....

15 Dec 2005 4:15 PM

Jakob Lithner

It seems it is the RETURN statement after the RAISERROR that resets the
@@ERROR to 0 again, making the calling procedure confused .....

What would you recommend?
Should I use RAISERROR or RETURN in the check procedure to inform the
calling procedure about the error?
I tend to the RAISERROR .....

15 Dec 2005 4:49 PM

Mark Williams

Since the procedure executes OK even if @LanguageID is NULL, @@ERROR in your
calling statement will always be 0. @@ERROR traps the error number for the
most recently executed statement.

You need something like this

CREATE PROCEDURE dbo.CheckLanguage
    @LanguageKey char(5),
    @LanguageID int OUT
AS

SET @LanguageID = NULL

-- Check if given language exists
SELECT @LanguageID = LanguageID
FROM Language
WHERE LanguageKey = @LanguageKey

IF @LanguageID IS NULL
BEGIN
    RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
16, 1, @LanguageKey)
    RETURN 1
END

Calling procedure contains this code:
......
-- Make sure the given language key is valid
DECLARE @return_status int
EXEC @return_status = CheckLanguage @LanguageKey, @LanguageID OUT
IF @return_status = 1
BEGIN
    SET @Response = 'Error_LanguageKey'
    RETURN 0
END
.....

Show quote

"Jakob Lithner" wrote:

> I am running SQL 2000.
> I have several procedures where I do general validation on entered values.
> User procedures typically call these "checkprocedures" to verify all entered
> values.
>
> I thought I grasped the idea of error handling but must have done something
> wrong.
> When a wrong value is found I throw an exception in the checking procedure
> and return an arbitrary return code other than 0, typically 1.
>
> In the calling procedure I check the @@ERROR value and then take action
> based on that. The problem is that excution does not stop after the exception
> is thrown even if I explicitly end with a Return in the calling procedure.
>
> What is wrong?
> Why does not the execution in the main procedure stop?
> Maybe it is overkill to use both exceptions and return values:
> Should I skip the exception throwing and rely on the Return values?
> Should I skip the Return values and rely on the exception throwing?
>
> My idea was to avoid defining the validation and exception messages on many
> places.
>
>
> Code example enclosed below:
>
> CREATE PROCEDURE dbo.CheckLanguage
>     @LanguageKey char(5),
>     @LanguageID int OUT
> AS
>
> SET @LanguageID = NULL
>
> -- Check if given language exists
> SELECT @LanguageID = LanguageID
> FROM Language
> WHERE LanguageKey = @LanguageKey
>
>
> IF @LanguageID IS NULL
> BEGIN
>     RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
> 16, 1, @LanguageKey)
>     RETURN 1
> END
> GO
>
>
> Calling procedure contains this code:
> .....
> -- Make sure the given language key is valid
> EXEC CheckLanguage @LanguageKey, @LanguageID OUT
> SET @ErrorNumber = @@ERROR
> IF @ErrorNumber <> 0
> BEGIN
>     SET @Response = 'Error_LanguageKey'
>     RETURN 0
> END
> ....

15 Dec 2005 9:09 PM

Jakob Lithner

Thanks, I guess that is the best solution!