-----------------------------------------------------------
New Message on MumbaiUserGroup
-----------------------------------------------------------
From: MVP_mayank
Message 2 in Discussion
How can you raise custom errors from stored procedure?<o:p></o:p>
<o:p> </o:p>
There are actually one or more ways to raise custom error messages in Sql
Server.<o:p></o:p>
Following are some ways which are commonly used with sql server<o:p></o:p>
<o:p> </o:p>
@@ Error method:
The @@ERROR system function returns 0 if the last procedure executed
successfully; if the statement generated an error, @@ERROR returns the error
number. The following example explains with an Insert statement, <o:p></o:p>
CREATE PROCEDURE sp_addEmployee
@empId varchar(10),@empName varchar(40),@phone char(12),@address varchar(40) =
NULL,
@city varchar(20) = NULL,@state char(2) = NULL,@zip char(5) = NULL
AS<o:p></o:p>
INSERT INTO tblEmployee
(fldEmpId, fldEmpName, fldPhone, fldAddress, fldCity, fldState, fldZip)
values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)<o:p></o:p>
IF @@ERROR <> 0 <o:p></o:p>
BEGIN
PRINT "An error occurred while adding the new Employee information"
RETURN(99)
END<o:p></o:p>
ELSE<o:p></o:p>
BEGIN
PRINT "The new author information has been loaded"
RETURN(0)
END<o:p></o:p>
GO<o:p></o:p>
In this example the IF...ELSE statements test @@ERROR after an INSERT statement
which inserts the employee details in a stored procedure. The value of the
@@ERROR variable determines the return code sent to the calling program,
indicating the success or failure of the procedure.
Using SP_ADDMESSAGE :
SP_ADDMESSAGE is a system stored procedure used to add a new error message to
the sysmessages table. This message could be a custom defined one. The syntax
for this stored procedure is,<o:p></o:p>
sp_addmessage [EMAIL PROTECTED] =] msg_id,
[EMAIL PROTECTED] =] severity,
[EMAIL PROTECTED] =] 'msg'
[, [EMAIL PROTECTED] =] 'language']
[, [EMAIL PROTECTED] =] 'with_log']
[, [EMAIL PROTECTED] =] 'replace']<o:p></o:p>
Here, [EMAIL PROTECTED] =] msg_id is the ID of the message; [EMAIL PROTECTED]
=] is the severity level of the error (severity is smallint) and value varies
from 0-25 as mentioned earlier; [EMAIL PROTECTED] =] 'msg' is the text of the
error message; [EMAIL PROTECTED] =] 'language' is the language for this
message, which helps to display the error message in multiple languages. [EMAIL
PROTECTED] =] is whether the message is to be written to the Microsoft® Windows
NT® application log when it occurs, the value will be true or false. [EMAIL
PROTECTED] =],If specified as the string REPLACE, an existing error message is
overwritten with new message text and severity level.
An example which adds a new error message is,<o:p></o:p>
EXEC sp_addmessage 50001, 16,
N'Give perscentage value between 1 to 10
Please reexecute with a more appropriate value.'<o:p></o:p>
The RAISERROR method:
Though you can use print statements, RAISERROR is a more powerful statement
than PRINT, for returning messages back to applications. In two ways RAISERROR
can return messages.
1. Using sp_addmessage a user-defined error message has been added to
master.dbo.sysmessages.
2. Using the message string specified in the RAISERROR statement.
The advantages of using RAISERROR over PRINT is it can assign a specific error
number, severity, and state. Moreover the error can be logged.<o:p></o:p>
The syntax for RAISERROR method is as follows,<o:p></o:p>
RAISERROR ({msg_id | msg_str}, severity, state
[, argument1 [, argument2]])
[WITH options]
Regards,
Mayank Pujara ([EMAIL PROTECTED])
Send Your Comments Here
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/mumbaiusergroup/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
