Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jwize
Starting Member
7 Posts |
 Posted - 2006-12-30 : 00:11:39
|
| I have a program that retrieves stats from a database of games.I have made stored procedures forMost wins Most wins in a dayLongest win streakLongest lose streamand 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
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-12-30 : 02:18:43
|
| 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 |
 |
|
|
jwize
Starting Member
7 Posts |
Posted - 2006-12-30 : 02:47:57
|
| 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. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-12-30 : 02:59:31
|
| 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 |
|
|
|
jwize
Starting Member
7 Posts |
Posted - 2006-12-30 : 08:05:27
|
| 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 GetLongestWinStreakEXEC GetLongestLoseStreakEXEC GetMoreStatsThe return all statistics as a result set. |
|
|
|
|
|
|
One big stored procedure on many small