Alternatives to Stored Procedure

• MaverickMan

  SSC Eights!

  Points: 925

  More actions

  August 21, 2009 at 8:43 am

  #214023

  My question here might get a bit vague without actually seeing the whole system however I will try to provide as much of detail as possible and would certainly provide any additional information that would be needed in advising me further:

  My company is a mid size company where we use extensive use of SQL Server reporting services. We make use of SQL Server as the database. Now we are migrating to the new SQL Server 2008/ SSRS 2008 environment. We have been making extensive use of stored procedures for creating reports. However there is a huge log stored procedures because we have been using a few base stored procedures and other branched stored procedures.

  Now with migration we would also like to bring about new ways to organize stored procedures or use alternatives to stored procedures in order to reduce the high number of stored procedure which get complicated when everyone starts creating innumerable branches. We started exploring other options like using UDF to reduce the use of stored procedures. However I learnt from my research that UDF's are not very scalable.

  1) Are there any alternatives to using stored procedures?

  2) Is there anyway we can UDF's and can make them scalable?

  3) Are there any other ways to organize stored procedures rather than branching them innumerably.

  Any advice would be appreciated!

  Thx.

• 465789psw

  Hall of Fame

  Points: 3314

  More actions

  August 21, 2009 at 9:15 am

  #1042909

  I would stay with the SP you could try to eliminate the branching....

  do this by reusing the base SP for each new SP. Just a copy and paste....I think I understand what you are trying to do though.

  I have the same problems. you end up with a SP that calls many SP with in it, if I am understanding you correctly... it almost like having triggers...gets a little messy to follow.

  It seems like the idea it right you just reusing code as in OOP. \Not sure of any other way around it

  good luck

• MaverickMan

  SSC Eights!

  Points: 925

  More actions

  August 21, 2009 at 9:24 am

  #1042915

  I think you pretty much got it right. Yea and you invariably end up calling one base stored proc from quite a lot of stored procs and it gets messy.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  August 21, 2009 at 9:26 am

  #1042918

  Stick with procs. It can get messy, but anything else gets even worse.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  August 23, 2009 at 6:04 pm

  #1043421

  I think that in the described scenario storedprocs are just the fever... the illness might be...

  MaverickMan (8/21/2009)

  ---

  ... get complicated when everyone starts creating innumerable branches.

  I would stay with storedprocs and try to better organize development effort.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  August 23, 2009 at 7:34 pm

  #1043424

  it's really not possible to give a good answer to this question without looking at some examples oif what you are seeing as "the problem". In particular, wht do the "branched" procs look like and why are your developers doing it this way?

  There are at least a half-a dozen different ways to do this, but which approach (or combination or approaches) has a lot to do with how and why you are ending up where you are.

  Here are just some of the ways that I know of and have used successfully in the past, and that might be right for you. Though all of these can avoid the scaling & performance problems of normal UDF's, which is right, again, depends.

• Individual sProcs
• Branched sProcs (internal)
• Branched sProcs (exnternal)
• Vector Executed sProcs
• Dynamic query executiion (**)
• Dynamic sproc execution (**)
• Views
• In-line Table-Valued Functions
• CLR Table-Valued Functions

( ** - despite what you may have heard, dynamic execution can be done very safely and performance, but it reuires structure and discipline ti insure this).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]