Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pravin.pawar
Starting Member
6 Posts |
 Posted - 2013-09-23 : 10:26:02
|
| Hi,We are working on error handling part in stored procedure and wanted to know how can we log errors and continue with next execution of the code.We are processing few records from source to destianiton table and there are few records in source table which has invalid data. We are perocessing records from source one by one within while loop and expecting to log invalid data and process next record in case of any errors without terminating the execution.Please let us know the best approachRegards, Pravin |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-23 : 10:52:23
|
| The recommended constructs for handling error conditions in SQL 2012 is to use TRY-CATCH blocks. See here: http://technet.microsoft.com/en-us/library/ms175976.aspxHowever, I usually use try-catch blocks to handle unexpected errors. For controlling the logic flow based on different types of data, some of which may be categorized as invalid or erroneous conditions, I would check for such data using whatever validity conditions are appropriate and take action based on that. For example, if I am trying to insert data into a table, and can insert it only if a certain column is not null, I would have a where clause in the insert statement that reads something like "WHERE column1 IS NOT NULL". Then, I would log/save the error records into a log table using a statement that has a where clause like "WHERE column1 IS NULL".Another thing you might want to consider is to examine whether you can process the data in a set-based manner rather than handling them one by one. SQL Server is excellent at handling set-based requests, but can perform poorly when you go row by row. In fact, people have coined the acronym RBAR (Row By Agonizing Row) to refer to the row by row processing in SQL Server. |
 |
|
|
pravin.pawar
Starting Member
6 Posts |
Posted - 2013-09-24 : 02:19:09
|
Thanks for your inputs. We are inserting records one by one using while loop and need to log errors for the record which has invalid data.Once we log error; it should continue processing next record. We tried somehow when we are reading errors; processing of next records are terminated.Please let us know how should we continue with next execution of records after error logThansk ,Pravinquote:
Originally posted by James K
The recommended constructs for handling error conditions in SQL 2012 is to use TRY-CATCH blocks. See here: http://technet.microsoft.com/en-us/library/ms175976.aspxHowever, I usually use try-catch blocks to handle unexpected errors. For controlling the logic flow based on different types of data, some of which may be categorized as invalid or erroneous conditions, I would check for such data using whatever validity conditions are appropriate and take action based on that. For example, if I am trying to insert data into a table, and can insert it only if a certain column is not null, I would have a where clause in the insert statement that reads something like "WHERE column1 IS NOT NULL". Then, I would log/save the error records into a log table using a statement that has a where clause like "WHERE column1 IS NULL".Another thing you might want to consider is to examine whether you can process the data in a set-based manner rather than handling them one by one. SQL Server is excellent at handling set-based requests, but can perform poorly when you go row by row. In fact, people have coined the acronym RBAR (Row By Agonizing Row) to refer to the row by row processing in SQL Server.
Regards, Pravin |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-24 : 08:27:42
|
| Without seeing the queries you are using and the exact text of the error messages, it is virtually impossible to offer any useful suggestions. |
|
|
|
|
|
|
|
|
Error handling in SQL 2012