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 spUpdateSecurityMaster,
Line 49
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
'Trading', table 'Exchanges', column 'IsoCode'.
Server: Msg 50000, Level 14, State 1, Procedure
spUpdateSecurityMaster, 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]

On 1 Oct 2003 06:41:42 -0700, Ja*******@hotmail.com (Jason) wrote:

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 spUpdateSecurityMaster,
Line 49
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
'Trading', table 'Exchanges', column 'IsoCode'.
Server: Msg 50000, Level 14, State 1, Procedure
spUpdateSecurityMaster, 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.

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]

Jason (Ja*******@hotmail.com) writes:

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 spUpdateSecurityMaster,
Line 49
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database
'Trading', table 'Exchanges', column 'IsoCode'.
Server: Msg 50000, Level 14, State 1, Procedure
spUpdateSecurityMaster, 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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jason]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>...

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.

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]

Jason (Ja*******@hotmail.com) writes:

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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp