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?
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.
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.
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.