Error handling in Stored procedure

I have some DML statements that can cause some errors. I want to handle
them gracefully and go ahead with the rest of the SQL statement. What
is the best approach for this?
Assuming that in a loop I am doing some delete that can cause some
foreign key violation.
Delete from MasterTable --This can cause error if there are child
tables refering to this .
How can I get the error message so that I can log it to a log table?
This is what I am doing
Open a cursor on Tabel List
While
BEGIN
@.DynamicSQL = 'Delete from ' + @.TableName--The tablename wil
change for each iteration
If @.@.Error<>0
Begin
--log error message
END
ELSE
Begin
--log Success message
END
END
I hope I have explained it well.
Thanks in advance> How can I get the error message so that I can log it to a log table?
In the client application. You can only get the error number at the TSQL lev
el, not the error
message. For more information, see the error handling articles at www.sommarskog.s
e.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQL novice" <balacr@.gmail.com> wrote in message
news:1132650496.640747.51460@.g14g2000cwa.googlegroups.com...
>I have some DML statements that can cause some errors. I want to handle
> them gracefully and go ahead with the rest of the SQL statement. What
> is the best approach for this?
> Assuming that in a loop I am doing some delete that can cause some
> foreign key violation.
> Delete from MasterTable --This can cause error if there are child
> tables refering to this .
> How can I get the error message so that I can log it to a log table?
> This is what I am doing
> Open a cursor on Tabel List
> While
> BEGIN
> @.DynamicSQL = 'Delete from ' + @.TableName--The tablename wil
> change for each iteration
> If @.@.Error<>0
> Begin
> --log error message
> END
> ELSE
> Begin
> --log Success message
> END
> END
>
> I hope I have explained it well.
> Thanks in advance
>|||Can I somehow force the next iteration to continue when there is some
error.
Here it is throwing the error and coming out when there is some error.
Thanks for your help|||Some type of errors will terminate the batch, most won't. It is all in the a
rticles I referred to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SQL novice" <balacr@.gmail.com> wrote in message
news:1132660588.888364.310040@.g47g2000cwa.googlegroups.com...
> Can I somehow force the next iteration to continue when there is some
> error.
> Here it is throwing the error and coming out when there is some error.
> Thanks for your help
>

Error Handling in Activation Procs

I am kinda curious how the rest of you are doing your error handling inside your activation stored procedures...best practices says you should not rollback the part of your transaction that receives the record off of the queue...but using a try...catch block will only allow you to rollback the entire transaction. I tried using savepoints and starting the try...catch after the savepoint and the proc still gives me the error: "

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." when the error scenario is encountered.

I tried using @.@.Error and checking for errors at the statement level which would allow me to do a partial rollback, but the type of errors I receive (i.e. invalid data types etc) are aborting the entire batch instead of passing the error and continuing.

Can other people on this forum using service broker give me an idea of how you are getting around this issue?

Hmmm...interesting. Where did you read the best practice about not rolling back the part of your code that receives the record off of the queue. I am not sure that makes sense to me. I would want to put the message back onto the queue if I know I have ran into an error.|||The idea is to avoid Poison Messaging where possible by accounting for whatever errors you know you will encounter (foreign key errors, Invalid Data types)...these types of errors will never be able to run and will therefore, bring down your queue every time if you rollback. The idea is to never roll back the receive, unless there is some type of fatal error that causes the batch to abort. In that case, as long as your receive is in a transaction, it will be rolled back automatically. If it is a handled error, you need to do something else with that message to move it off the queue.|||

On one of our systems we use seperate transactions for recieving the message and processing the message. The transaction around the receive simply removes the message from the queue and logs it to an audit table. If this step fails then we rollback and put the message back onto the queue. If we successfully receive and log the message we then begin another transaction (if applicable) for the processing of the message. Therefore if this fails for some reason (ie constraints) then we just rollback the processing and not the receive from the queue - we can then get the message from the audit table to re-process if required. Our idea is to keep the code in the transaction that includes the receive from the queue as simple as possible to avoid failures and prevent rollbacks - therefore avoiding poison messages. I'm not sure if this would be applicable in your case.