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. ALTER procedure usp_masterproc as DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int , @errorNum int Begin try begin transaction exec sp1; exec sp2; exec sp3; exec sp4; exec sp5; exec sp6 commit; end try begin catch if @@trancount >0 rollback RAISERROR(@ErrMsg, @ErrSeverity, 16) SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY(), @errorNum = ERROR_NUMBER() PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR); PRINT 'Error Message: ' + ERROR_MESSAGE(); PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR); PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR); PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR); PRINT 'Error Proc: ' + ERROR_PROCEDURE(); END CATCH
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.