x

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
more ▼

asked Apr 24, 2012 at 09:32 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

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
Apr 25, 2012 at 06:37 AM WilliamD
This book is also a good resource on this sort of consideration - http://www.red-gate.com/community/books/defensive-database-programming
Apr 25, 2012 at 08:46 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Apr 24, 2012 at 10:10 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.8k 19 21 74

+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, 2012 at 11:31 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x985
x408

asked: Apr 24, 2012 at 09:32 PM

Seen: 2569 times

Last Updated: Apr 25, 2012 at 11:31 AM