gracefully handling stored procedure errors

[William F. Robertson, Jr.]

One of my developers came to me with this question and I don't have an
answer for them. The only suggestion I had for them was to change the
return to a output parameter and put a try catch around it and look for the
output parameter to have a value.

I would have thought this should have been posted in the sql newgroups, but
I know exactly what they would say, and they would be correct.

But when using asp.net ado.net command object. I am running a sproc that
handles its own errors gracefully and will sets the output parameter @msg to
a successful, or unsucessful message.

And it does return it when I run the sproc in query analyzer.

However, when I run the code in asp.net the Execute method for the Command
object throws and exception so I never get the status.

Is there a way in a stored procedure to clear the error so it doesn't
through an exception through the ado.net object?

bill

<sproc snippet>

Insert Into tblTPA

Values( @FinalID, GetDate(), @InAuditor, @Type, @Comments, @Hours,
@Expenses, @DinersExpenses)

if @@Error <> 0

BEGIN

ROLLBACK

Set @msg = 'The final timesheet you tried to save was already saved by
another user.'

END

else

BEGIN

COMMIT

Set @msg = 'The final timesheet has been saved.'

END

Select @msg

[Nicole Calinoiu]

William,

The real problem here is that your stored procedure isn't really handling
the error condition "gracefully". Known potential error conditions that
should not be treated as application exceptions should not be allowed to
occur at all. If you don't want any part of your application treating this
potential duplicate insertion scenario as an exception, test for it in the
stored procedure before the insertion attempt. e.g.:

IF <final timesheet has already been saved>
SET @msg = 'The final timesheet you tried to save was already saved by
another user.'
ELSE
BEGIN
INSERT INTO...
IF @@ERROR <> 0 SET @msg = 'An unexpected error occurred.'
...
END

HTH,
Nicole
"William F. Robertson, Jr." <wf*********@kpmg.com> wrote in message
news:ei**************@TK2MSFTNGP09.phx.gbl...

One of my developers came to me with this question and I don't have an
answer for them. The only suggestion I had for them was to change the
return to a output parameter and put a try catch around it and look for the output parameter to have a value.

I would have thought this should have been posted in the sql newgroups, but I know exactly what they would say, and they would be correct.

But when using asp.net ado.net command object. I am running a sproc that
handles its own errors gracefully and will sets the output parameter @msg to a successful, or unsucessful message.

And it does return it when I run the sproc in query analyzer.

However, when I run the code in asp.net the Execute method for the Command
object throws and exception so I never get the status.

Is there a way in a stored procedure to clear the error so it doesn't
through an exception through the ado.net object?

bill

<sproc snippet>

Insert Into tblTPA

Values( @FinalID, GetDate(), @InAuditor, @Type, @Comments, @Hours,
@Expenses, @DinersExpenses)

if @@Error <> 0

BEGIN

ROLLBACK

Set @msg = 'The final timesheet you tried to save was already saved by
another user.'

END

else

BEGIN

COMMIT

Set @msg = 'The final timesheet has been saved.'

END

Select @msg

[Lyndon Hills]

On Fri, 24 Oct 2003 16:44:49 -0500, "William F. Robertson, Jr."
<wf*********@kpmg.com> wrote:

One of my developers came to me with this question and I don't have an
answer for them. The only suggestion I had for them was to change the
return to a output parameter and put a try catch around it and look for the
output parameter to have a value.

I would have thought this should have been posted in the sql newgroups, but
I know exactly what they would say, and they would be correct.

But when using asp.net ado.net command object. I am running a sproc that
handles its own errors gracefully and will sets the output parameter @msg to
a successful, or unsucessful message.

And it does return it when I run the sproc in query analyzer.

However, when I run the code in asp.net the Execute method for the Command
object throws and exception so I never get the status.

Is there a way in a stored procedure to clear the error so it doesn't
through an exception through the ado.net object?

bill

<sproc snippet>

Insert Into tblTPA

Values( @FinalID, GetDate(), @InAuditor, @Type, @Comments, @Hours,
@Expenses, @DinersExpenses)

if @@Error <> 0

BEGIN

ROLLBACK

Set @msg = 'The final timesheet you tried to save was already saved by
another user.'

END

else

BEGIN

COMMIT

Set @msg = 'The final timesheet has been saved.'

END

Select @msg

Would it be better for the stored proc to raise an exception of it's
own? You can specify the message, to be user friendly, and will be
able to access it in the catch block. This will work with sql server
2K, I wouldn't know about previous versions.

A further point is that there are some errors you can't trap -
duplicate key being one, so if you want nice messages for this the
stored proc must detect that it was going to happen and not try the
insert.

[William F. Robertson, Jr.]

Yes, you were right, that was the real problem.

I should have seen that.

Thanks,
bill

"Nicole Calinoiu" <ni*****@somewhere.net> wrote in message
news:em*************@TK2MSFTNGP10.phx.gbl...

William,

The real problem here is that your stored procedure isn't really handling
the error condition "gracefully". Known potential error conditions that
should not be treated as application exceptions should not be allowed to
occur at all. If you don't want any part of your application treating this potential duplicate insertion scenario as an exception, test for it in the
stored procedure before the insertion attempt. e.g.:

IF <final timesheet has already been saved>
SET @msg = 'The final timesheet you tried to save was already saved by
another user.'
ELSE
BEGIN
INSERT INTO...
IF @@ERROR <> 0 SET @msg = 'An unexpected error occurred.'
...
END

HTH,
Nicole
"William F. Robertson, Jr." <wf*********@kpmg.com> wrote in message
news:ei**************@TK2MSFTNGP09.phx.gbl...

One of my developers came to me with this question and I don't have an
answer for them. The only suggestion I had for them was to change the
return to a output parameter and put a try catch around it and look for the

output parameter to have a value.

I would have thought this should have been posted in the sql newgroups,

but

I know exactly what they would say, and they would be correct.

But when using asp.net ado.net command object. I am running a sproc that
handles its own errors gracefully and will sets the output parameter @msg to

a successful, or unsucessful message.

And it does return it when I run the sproc in query analyzer.

However, when I run the code in asp.net the Execute method for the

Command object throws and exception so I never get the status.

Is there a way in a stored procedure to clear the error so it doesn't
through an exception through the ado.net object?

bill

<sproc snippet>

Insert Into tblTPA

Values( @FinalID, GetDate(), @InAuditor, @Type, @Comments, @Hours,
@Expenses, @DinersExpenses)

if @@Error <> 0

BEGIN

ROLLBACK

Set @msg = 'The final timesheet you tried to save was already saved by
another user.'

END

else

BEGIN

COMMIT

Set @msg = 'The final timesheet has been saved.'

END

Select @msg

[William F. Robertson, Jr.]

Yes, you were right, that was the real problem.

I should have seen that.

Thanks,
bill

"Nicole Calinoiu" <ni*****@somewhere.net> wrote in message
news:em*************@TK2MSFTNGP10.phx.gbl...

William,

The real problem here is that your stored procedure isn't really handling
the error condition "gracefully". Known potential error conditions that
should not be treated as application exceptions should not be allowed to
occur at all. If you don't want any part of your application treating this potential duplicate insertion scenario as an exception, test for it in the
stored procedure before the insertion attempt. e.g.:

IF <final timesheet has already been saved>
SET @msg = 'The final timesheet you tried to save was already saved by
another user.'
ELSE
BEGIN
INSERT INTO...
IF @@ERROR <> 0 SET @msg = 'An unexpected error occurred.'
...
END

HTH,
Nicole
"William F. Robertson, Jr." <wf*********@kpmg.com> wrote in message
news:ei**************@TK2MSFTNGP09.phx.gbl...

One of my developers came to me with this question and I don't have an
answer for them. The only suggestion I had for them was to change the
return to a output parameter and put a try catch around it and look for the

output parameter to have a value.

I would have thought this should have been posted in the sql newgroups,

but

I know exactly what they would say, and they would be correct.

But when using asp.net ado.net command object. I am running a sproc that
handles its own errors gracefully and will sets the output parameter @msg to

a successful, or unsucessful message.

And it does return it when I run the sproc in query analyzer.

However, when I run the code in asp.net the Execute method for the

Command object throws and exception so I never get the status.

Is there a way in a stored procedure to clear the error so it doesn't
through an exception through the ado.net object?

bill

<sproc snippet>

Insert Into tblTPA

Values( @FinalID, GetDate(), @InAuditor, @Type, @Comments, @Hours,
@Expenses, @DinersExpenses)

if @@Error <> 0

BEGIN

ROLLBACK

Set @msg = 'The final timesheet you tried to save was already saved by
another user.'

END

else

BEGIN

COMMIT

Set @msg = 'The final timesheet has been saved.'

END

Select @msg