Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error handling in SQL server Stored proc Expand / Collapse
Author
Message
Posted Wednesday, April 1, 2009 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 6, Visits: 108
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
Posted Wednesday, April 1, 2009 10:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, 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
Posted Wednesday, April 1, 2009 11:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 6, Visits: 108
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
Posted Wednesday, April 1, 2009 11:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Thursday, April 2, 2009 4:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
As Mithun wrote you should have a look to TRY-CATCH blocks in SQL Server 2005.

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 #688712
Posted Thursday, April 2, 2009 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:02 AM
Points: 6, Visits: 108
I'm using SQL Server 2000 . What is the best way to handle DML statement exceptions in stored proc. ?

Post #689187
Posted Thursday, April 2, 2009 11:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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 »

Add to briefcase

Permissions Expand / Collapse