April 1, 2009 at 9:21 am
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
April 1, 2009 at 10:21 am
Hi
A little example. Your procedure:
CREATE PROCEDURE usp_MyErrorProc
AS
RAISERROR ('Hello, I''m a custom database error.', 11, 1)
In .Net:
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();
}
}
Greets
Flo
April 1, 2009 at 11:20 pm
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
April 1, 2009 at 11:45 pm
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
April 2, 2009 at 4:40 am
As Mithun wrote you should have a look to TRY-CATCH blocks in SQL Server 2005.
Greets
Flo
April 2, 2009 at 11:23 am
I'm using SQL Server 2000 . What is the best way to handle DML statement exceptions in stored proc. ?
April 2, 2009 at 11:53 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply