SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error handling in SQL server Stored proc


Error handling in SQL server Stored proc

Author
Message
vyankarla
vyankarla
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 158
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
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3445 Visits: 3934
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
vyankarla
vyankarla
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 158
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
mithun.gite
mithun.gite
Old Hand
Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)

Group: General Forum Members
Points: 381 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
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3445 Visits: 3934
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
vyankarla
vyankarla
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 158
I'm using SQL Server 2000 . What is the best way to handle DML statement exceptions in stored proc. ?
Vijaya Kadiyala
Vijaya Kadiyala
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1885 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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search