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. 4 14965
On 1 Oct 2003 06:41:42 -0700, Ja*******@hotma il.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 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.
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.
Jason (Ja*******@hotm ail.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 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.
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
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************@1 27.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.
Jason (Ja*******@hotm ail.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mihaly |
last post by:
I have a stored procedure in SQL Server 2000, and I want to read the error
messages from this stored procedure.
Please help me for this question:
This is the stored procedure. Please supose than Column2 has no 0 values:
CREATE PROCEDURE dbo.ErrorTest
AS
SELECT Column1 FROM Table WHERE Column2 = 0
IF (@@ROWCOUNT <= 0) GOTO RollB
|
by: Helen |
last post by:
I've created a custom error page for an ASP.NET application. I'm
wondering if there's anyway for my error page to find out what actual
exception was thrown? I'd like to be able to display different error
messages for different exception types in the hope that I'll be able
to give the users enough information to fix any problems that were...
|
by: Jacob |
last post by:
Hi,
How can i store custom error messages, can i store it in a resource file ,
like ErMsgs.Resx.
if i store it in a .resx file, can i modify it without recompiling, if so
how can i change it without recompiling??
thanks in advance .
|
by: BARTKO Zoltan |
last post by:
Folks,
I am developing an app for PostgreSQL, entirely with stored functions (somewhat resembling the object-oriented approach. In fact it is not an app, just an API). All my functions return an integer value - 0 if the function wassuccessful, another value otherwise.
Until now, all parameter checking was done manually in the stored function...
|
by: bdockery |
last post by:
Here's yet another one Mccarthy and Rabbit :)
I'm sure you know this since you know everything!
Do you just have to have error-checking code that pops up a msgBox and prevents the error in the first place, or is there actually a method to modify error messages?
| |
by: vikram1102 |
last post by:
can we provide custom error messages in place of error codes in db2?
|
by: Artie |
last post by:
Hi,
Is there any way to tell an XSD Schema that you want custom error
codes for particular validation failures in an XML document?
To show what I mean, here's an example:
XSD excerpt
<xs:element name = "EmployeeID">
|
by: Artie |
last post by:
On Apr 21, 11:48 am, Martin Honnen <mahotr...@yahoo.dewrote:
Thanks for your reply Martin.
I was hoping for something generic rather than validator-specific, but
I'll try in a Microsoft group as well.
Cheers
Artie
|
by: hyperpau |
last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge.
I would be using layman's...
|
by: hyperpau |
last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge.
I would be using layman's...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |