Raise custom errors from stored procedure, DOT NET Programming

How can you raise custom errors from stored procedure?

The RAISERROR statements are  used to produce an ad hoc error message or to retrieve acustom message which is stored in the sysmessages table. You can use this statement with the error handling code presented in the previous part to implement custom error messages in your applications. The syntax of the statement is shown below.

RAISERROR ({msg_id |msg_str }{,severity ,state }

[ ,argument [ ,,...n ] ] ))

[ WITH option [ ,,...n ] ]

The description of the components of the statement is as shown below:

msg_id :-This ID is used for an error message,that is stored in the error column in the sysmessages.

msg_str :-This shows a custom message that is not contained in sysmessages.

severity :- The severity level is associated with the error. The valid values are at 0-25.The Severity levels 0-18 can be used by any user, but 19-25 are only available to those members of the fixed-server role sysadmin. When the levels 19-25 are used, then the WITH LOG option is required.

The state A value that shows the invocation state of the error. The valid values are 0-127. That  value is not used by the SQL Server.

Argument, . . .

It is used by One or more variables that are used to customize the message. For e.g., you could pass the current process ID (@@SPID) so it could be displayed in the message.

WITH option, . . .

The three values that are used with this optional argument are described here.

LOG - It Forces the error to log in the SQL Server error log and the NT application log.

NOWAIT - This Sends the message immediately to the client.

SETERROR - This Sets @@ERROR to the unique ID for the message or 50,000.

The number of options available for the statement made it seem complicated, but it is actually simple to use. The following shows how to create an ad hoc message with the severity of 10 and a state of 1.

RAISERROR ('An error occurred during updating the NonFatal table',10,1)

--Results--

An error definitely occurred on updating the NonFatal table.The statement does not have to be used in the conjunction with any other code, but for our purposes it will be used with the error handling code presented earlier. The shown below alters the ps_NonFatal_INSERT procedure to use RAISERROR.

USE tempdb go

ALTER PROCEDURE ps_NonFatal_INSERT

@Column2 int =NULL AS

DECLARE @ErrorMsgID int

INSERT NonFatal VALUES (@Column2) SET @ErrorMsgID =@@ERROR

IF @ErrorMsgID <>0

BEGIN

RAISERROR ('An error occured updating the NonFatal table',10,1) END

Posted Date: 9/24/2012 4:04:30 AM | Location : United States







Related Discussions:- Raise custom errors from stored procedure, Assignment Help, Ask Question on Raise custom errors from stored procedure, Get Answer, Expert's Help, Raise custom errors from stored procedure Discussions

Write discussion on Raise custom errors from stored procedure
Your posts are moderated
Related Questions
What is an interface and what is an abstract class? Please, expand by examples of using both. Explain why.   In a interface class, all methods are abstract without execut

I need Custom Login Page for Sharepoint 2013 with Active Directory Project Description: I would like to prepare a webpart or an application for Sharepoint 2013 foundation.

Mention the name of the directory where it is essential to locate the proxy file to use a Web service. The proxy file must be kept in the /bin directory. This directory is situ

What does WSDL stand for?  WSDL stands for Web Services Description Language.  It is an XML representation of the web service interface. There are two parts of the operation

Install Modules to enhance Site Proformance Project Description: As shown, I need the subsequent modules installed, to improve loading time for my graphics and images, improv

Write a Program of Changing String Order - C# Language  using System;   class Prog3_2  {       public static void Main(String [] args)    {     Console.Write(ar

Custom Login Page for SharePoint 2013 with Active Directory Project Description: I would like to prepare a webpart or an application for Sharepoint 2013 foundation. The we

Asp.net expert and simple html I am not new to the industry. I am seeking a long term asp.net developer to build and maintain a volleyball player management website. We would

What is the difference between VB6 and VB.Net? VB6 1.       It is Object-based Language 2.       It Does not support Threading 3.       Not powerful Exception handli

What is the relationship between a class and an object? A class acts as a blue-print that explains the properties, states, and behaviors that are common to a number of objects.