asked
multiple stored procedures
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
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][1]" (This still applies to SQL 2008 and 2008R2). [1]:
http://www.sommarskog.se/error_handling_2005.html
This book is also a good resource on this sort of consideration -
http://www.red-gate.com/community/books/defensive-database-programming
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!
6 People are following this question.
