SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 One big stored procedure on many small
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

jwize
Starting Member

7 Posts

Posted - 12/30/2006 :  00:11:39  Show Profile  Visit jwize's Homepage  Click to see jwize's MSN Messenger address
I have a program that retrieves stats from a database of games.

I have made stored procedures for

Most wins
Most wins in a day
Longest win streak
Longest lose stream
and so on..

and so on. It seems like it would be best to write them all up in one stored procedure rather than many small ones. I can't see my self reusing the code for anything else, nor are the stats needed on a more granular basis. Am I going about it in the correct manner by grouping them all together in one proc?

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/30/2006 :  02:18:43  Show Profile
it seems logical that this could all be one procedure. You could simply pass different arguments to the proc depending on what you were wanting for output.



-ec
Go to Top of Page

jwize
Starting Member

7 Posts

Posted - 12/30/2006 :  02:47:57  Show Profile  Visit jwize's Homepage  Click to see jwize's MSN Messenger address
Thanks, I just needed that to confirm what I think I already know. It just takes more data access layer code to keep everything in the same connection with the multiple stored procedures, plus it seems like there would be a communication hit with multiple stored procedures if I call them from my dal. I made a single facade type procedure to call all of the others from the database code I assume that the impact would be minimal, but only useful if I intended to use the code in other applications.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/30/2006 :  02:59:31  Show Profile
keep in mind that there is no right or wrong answer to your question. You might have a situation that necessitates using separate stored procs. Maybe you have an extremely busy site or some other constraint that necessitates this.

In any case, to me it makes sense to try and logically group these functions under one procedure since in all likelihood they are very similar queries. In fact, that is a very common practice with TSQL coding. Since Microsoft doesn't support the PACKAGE concept for stored procs (like Oracle does) we are stuck with making multi-use type procedures or a bunch of little procs.

My suggestion to you is to use a IF/THEN blocks for the different arguments you pass a procedure like this. Try to stay away from using dynamic SQL in your stored procedures. If you use dynamic sql you will reparse the proc each time it executes.



-ec
Go to Top of Page

jwize
Starting Member

7 Posts

Posted - 12/30/2006 :  08:05:27  Show Profile  Visit jwize's Homepage  Click to see jwize's MSN Messenger address
What do you mean by dynamic queries?


There is no need for IF ELSE statements in my case since the only in parameter is the user. All the stats only apply to the user in question. I suppose if I wanted to query only certain statistics if then else would be appropriate.

The one problem with that is that if I need to add more queries I need to add more code and out parameters for client code and "one size fits all proc". If I return a row from each stored procedure for each of my stats, then I feed them to my facade entry procedure. The facade would call each of the stored procedures in turn returning a result set of statistic name and value pairs.

Is there a problem with this pattern?


EXEC GetLongestWinStreak
EXEC GetLongestLoseStreak
EXEC GetMoreStats

The return all statistics as a result set.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000