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.
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.
answered Apr 24 '12 at 10:10 PM
Grant Fritchey ♦♦