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
>