Error handling in SQL server Stored proc

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Error handling in SQL server Stored proc

Error handling in SQL server Stored proc

Rate Topic

Display Mode

Topic Options

Author

Message

vyankarla

Posted Wednesday, April 01, 2009 9:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 5, Visits: 58

I'm raising a user defined error message using RaiseError statement .

-- ********** STEP 3 : Insert into Member_Employee ************************************************************************************

INSERT INTO Member_Employee ( Member_Serial,
BUnit_Serial
) VALUES (
@t_MemberID,
@BUnit_Serial)

SELECT @t_ErrorNo =@@ERROR
if @t_Errorno<>0 begin
SET @t_ErrorDesc=' Error Generated while Adding info to Member_Employee Table'
GOTO ErrHand
end

SELECT @t_Member_EmpID =@@IDENTITY

--------------****************************** ERROR HANDLER ******************************************
ErrHand:

IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK

-- Raise an error and return
RAISERROR (@t_ErrorDesc, 16, 1)
RETURN
END

-- **************************************************

I am executing stored proc from asp.net application and I would like to catch the value from Raiseerror statement. I was able to know if it is a success or failure , but not the errordescription.

--> one way to catch the error description is by declaring @t_Errordesc as output variable with out using that in Raiseerror statement..

How exactly we need to use raiseerror statement and also how to do access this return value using asp.net.

Thanks
Venu

Post #688078

Florian Reischl

Posted Wednesday, April 01, 2009 10:21 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Hi

A little example. Your procedure:

CREATE PROCEDURE usp_MyErrorProc
AS
   RAISERROR ('Hello, I''m a custom database error.', 11, 1)

In .Net:
[code="csharp"]
private void Button_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection cn = new SqlConnection("Server=(local);Database=Sandbox;Integrated Security=SSPI;"))
{
cn.Open();

using (SqlCommand cmd = new SqlCommand("usp_MyErrorProc", cn))
{
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}

cn.Close();
}
}
[/code]

Greets
Flo

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #688147

vyankarla

Posted Wednesday, April 01, 2009 11:20 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 5, Visits: 58

Thank You.

I have intentionally included a failed insert statement and then tried to catch the raiseerror message from asp.net application per your instructions.

When I execute the code , it is throwing SQL system message as

Cannot insert the value NULL into column 'Comments', table 'GisClient.dbo.TestError'; column does not allow nulls. INSERT fails. The statement has been terminated.

Looks like it is not checking next statements when INSERT command is failed .. I have written something like this in proc.

CREATE PROCEDURE usp_MyErrorProc
AS

BEGIN

Declare @t_ErrorNo int
Declare @t_ErrorDesc Varchar(250)

SET NOCOUNT ON

INSERT INTO TESTERROR (Message) Values ('Hello ..This is a test ')

SET @t_ErrorNo =@@ERROR
if @t_Errorno<>0 begin
SET @t_ErrorDesc=' Error Generated while executing INSERT statement'
GOTO ErrHand
end

--------------****************************** ERROR HANDLER ******************************************
ErrHand:

IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK TRANSACTION

-- Raise an error and return
RAISERROR (@t_ErrorDesc, 16, 1)

RETURN

END

END
GO

Post #688536

mithun.gite

Posted Wednesday, April 01, 2009 11:45 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 2:15 AM
Points: 327, Visits: 364

if u want u can use

Begin try ....End try and Begin catch...End Catch

In Try block put ur insert statament and in catch block put ur rollback and raiserror statament...

Mithun

Post #688545

Florian Reischl

Posted Thursday, April 02, 2009 4:40 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

As Mithun wrote you should have a look to TRY-CATCH blocks in SQL Server 2005.

Greets
Flo

Post #688712

vyankarla

Posted Thursday, April 02, 2009 11:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 5, Visits: 58

I'm using SQL Server 2000 . What is the best way to handle DML statement exceptions in stored proc. ?

Post #689187

Vijaya Kadiyala

Posted Thursday, April 02, 2009 11:53 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, Visits: 409

Hi

Before start doing this, first you need to find out what kind of errors you are expecting!!! then code it accordngly.

Check out the below link
http://www.sql-server-performance.com/articles/dev/exception_handling_p1.aspx

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Post #689213

« Prev Topic | Next Topic »

Permissions