Error handling problem

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 exceptio
n
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
...It seems it is the RETURN statement after the RAISERROR that resets the
@.@.ERROR to 0 again, making the calling procedure ....
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 ....|||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
...
"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 enter
ed
> values.
> I thought I grasped the idea of error handling but must have done somethin
g
> 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 except
ion
> 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 man
y
> 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
> ...|||Thanks, I guess that is the best solution!