Custom Error Messages

[Jason]

My understanding is that in a stored procedure (or any code for that
matter) if an error occurs you can detect it by checking @@error
variable and raise your own error with raiserror statement.

The problem is that the original error is not suppressed. For example
I received the following output from a stored procedure from the same
error:

Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurit yMaster,
Line 49
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_SM_mm_Excha nge_Exchanges'. The conflict occurred in database
'Trading', table 'Exchanges', column 'IsoCode'.
Server: Msg 50000, Level 14, State 1, Procedure
spUpdateSecurit yMaster, Line 57
Unable to insert into "SM_mm_Exchange " table
The statement has been terminated.

So why should we bother to use raiseerror if the orginal error is
going to be given to the client anyways? The end result is two error
messages.

[Lyndon Hills]

Re: Custom Error Messages

On 1 Oct 2003 06:41:42 -0700, JayCallas@hotma il.com (Jason) wrote:
[color=blue]
>My understanding is that in a stored procedure (or any code for that
>matter) if an error occurs you can detect it by checking @@error
>variable and raise your own error with raiserror statement.
>
>The problem is that the original error is not suppressed. For example
>I received the following output from a stored procedure from the same
>error:
>
>Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurit yMaster,
>Line 49
>INSERT statement conflicted with COLUMN FOREIGN KEY constraint
>'FK_SM_mm_Exch ange_Exchanges' . The conflict occurred in database
>'Trading', table 'Exchanges', column 'IsoCode'.
>Server: Msg 50000, Level 14, State 1, Procedure
>spUpdateSecuri tyMaster, Line 57
>Unable to insert into "SM_mm_Exchange " table
>The statement has been terminated.
>
>So why should we bother to use raiseerror if the orginal error is
>going to be given to the client anyways? The end result is two error
>messages.[/color]
The reason is that SQL Server will not allow you to trap errors of a
certain severity.

If the error was less severe then what you want to happen will work.

[Erland Sommarskog]

Re: Custom Error Messages

Jason (JayCallas@hotm ail.com) writes:[color=blue]
> My understanding is that in a stored procedure (or any code for that
> matter) if an error occurs you can detect it by checking @@error
> variable and raise your own error with raiserror statement.
>
> The problem is that the original error is not suppressed. For example
> I received the following output from a stored procedure from the same
> error:
>
> Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurit yMaster,
> Line 49
> INSERT statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_SM_mm_Excha nge_Exchanges'. The conflict occurred in database
> 'Trading', table 'Exchanges', column 'IsoCode'.
> Server: Msg 50000, Level 14, State 1, Procedure
> spUpdateSecurit yMaster, Line 57
> Unable to insert into "SM_mm_Exchange " table
> The statement has been terminated.
>
> So why should we bother to use raiseerror if the orginal error is
> going to be given to the client anyways? The end result is two error
> messages.[/color]

It's actually even three. That last "The statement has been terminated"
is a separate message.

No, there is not much with a RAISERROR here. But there might be
occasions where RAISERROR is your sole choice. Say that you have a
procedure that has an parameter that controls the logic, and it
have have the values A, B and C. You procedure would look like:

IF @action = 'A'
BEGIN
...
END
ELSE IF @action = 'B'
BEGIN
...
END
ELSE IF @action = 'C'
BEGIN
...
END
ELSE
BEGIN
RAISERROR ('Illegal action "%s" passed!', 16, 1, @action)
RETURN 1
END

And, no there is no way to suppress the error message from SQL. You
need a client to do that.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Jason]

Re: Custom Error Messages

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns9408F0 145FFYazorman@1 27.0.0.1>...[color=blue]
>
> It's actually even three. That last "The statement has been terminated"
> is a separate message.
>
> No, there is not much with a RAISERROR here. But there might be
> occasions where RAISERROR is your sole choice. Say that you have a
> procedure that has an parameter that controls the logic, and it
> have have the values A, B and C. You procedure would look like:
>
> IF @action = 'A'
> BEGIN
> ...
> END
> ELSE IF @action = 'B'
> BEGIN
> ...
> END
> ELSE IF @action = 'C'
> BEGIN
> ...
> END
> ELSE
> BEGIN
> RAISERROR ('Illegal action "%s" passed!', 16, 1, @action)
> RETURN 1
> END
>
> And, no there is no way to suppress the error message from SQL. You
> need a client to do that.[/color]

I figured that. So basically it makes no sense to use raiserror when a
database statement (delete, insert, update) is used since one will
already be thrown.

[Erland Sommarskog]

Re: Custom Error Messages

Jason (JayCallas@hotm ail.com) writes:[color=blue]
> I figured that. So basically it makes no sense to use raiserror when a
> database statement (delete, insert, update) is used since one will
> already be thrown.[/color]

Yes, it would be fairly redundant. I guess there might be occassions
where it could make sense, for instance convey information about what
was going on, like "Error when adding account 98989". But as a matter
of routine, it would be pointless.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp