SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error handling in SQL 2012
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pravin.pawar
Starting Member

India
6 Posts

Posted - 09/23/2013 :  10:26:02  Show Profile  Reply with Quote
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 approach


Regards, Pravin

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 09/23/2013 :  10:52:23  Show Profile  Reply with Quote
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.aspx

However, 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.
Go to Top of Page

pravin.pawar
Starting Member

India
6 Posts

Posted - 09/24/2013 :  02:19:09  Show Profile  Reply with Quote

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 log

Thansk ,Pravin

quote:
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.aspx

However, 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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 09/24/2013 :  08:27:42  Show Profile  Reply with Quote
Without seeing the queries you are using and the exact text of the error messages, it is virtually impossible to offer any useful suggestions.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000