|
if i had multiple procedures as mentioned in the code, for the code maintainance purposes, can i just take the block of code in each of the statements into multiple begin and end blocks, so that all the required code for this task stays in one single stored procedures, instead of 5? and also what are the disadvantages or the pit falls of doing mutltiple begin and end block way.
(comments are locked)
|
|
Can you do this? Yes. The problem you'll run into is that you're looking at the possibility of more statement recompiles because the parameters that get passed in are the ones that will be used to generate ALL the plans, even if those statements aren't actually referenced. Also, you could see very poor performance because bad plans are created because of the same issue, parameter values that aren't used or represent data sets that won't normally be referenced. Plus, it's actually easier to maintain code broken down into smaller chunks. And you have the ability to use those same procs in other situations. I would absolutely keep it the way it is. Otherwise you sacrifice too much flexibility and you're possibly introducing performance issues. +1 I agree with you, there are many procedures that doesn't perform well and should be broken down to smaller pieces. How do you eat an elefant? In small pieces! :) The same theory can be applied to sql server programming!
Apr 25 '12 at 11:31 AM
Håkan Winther
(comments are locked)
|


I agree with @Grant's answer, but also noticed your error handling in the CATCH block. Please be aware that you need to consider your error and transaction state before rolling back, there are cases where this will fail.
I suggest taking a look at the articles on error handling written by Erland Sommarskog, especially "Error Handling in SQL 2005" (This still applies to SQL 2008 and 2008R2).
This book is also a good resource on this sort of consideration - http://www.red-gate.com/community/books/defensive-database-programming