Is it better to write a more targeted stored procedure with fewer parameters?

Question

Say I have a stored procedure that returns data from a SELECT query. I would like to get a slightly different cut on those results depending on what parameters I pass through. I'm wondering whether it is better design to have multiple stored procedures that take one or no parameters to do this (for example, GetXByDate or GetXByUser), or one stored procedure with multiple parameters that does the lot (for example, GetX)?

The advantage of the first option is that it's simpler and maybe faster, but disadvantage is that the essence of the query is duplicated across the stored procedures and needs to be maintained in several places.

The advantage of the second option is that the query is only present once, but the disadvantage is that the query is more complex and harder to troubleshoot.

What do you use in your solutions and why? Thanks.

Answer 1

Who/what will be calling these stored procedures? I wouldn't write stored procedures for SELECT statements normally, precisely because there are lots of different SELECT statements you might want, including joins to other tables etc.

Answer 2

I prefer GetXByDate, GetXByUser, ... for simple stored procedures, on the basis they will require little maintenance anyway, and in this situation I think it is easier to maintain duplicate code than complicated code.

Of course, if you have more complicated stored procedures, this may not be true. GetAndProcessXByDate may be better reduced to GetXByDate, GetXByUser, ... which call another stored proc ProcessX.

So I guess the definitive answer is: it depends... :)

Answer 3

The more complex stored procedures are more complex for the SQL server to compile correctly and execute quickly and efficiently.

Even in the big stored procedure you have to either have to have several copies of the query or add lots of CASEs and IFs in it which reduce performance. So you don't really gain much from lumping everything together.

From my personal experience I also consider large SQL sp code with lots of branches more difficult to maintain that several smaller and straightforward sprocs.

You could consider using views and UDFs to reduce copy-pasting of the query code.

Saying that if you don't care about performance (intranet app, the queries are not that heavy, don't run that often) you might find having a universal sproc quite handy.

Answer 4

I would treat stored procedures much in the same way as I would a method on a class. It ought to do one thing and do it simply. Consider applying the same sorts of refactoring/code smell rules to your stored procedures that you would to your application code.

Answer 5

One advantage of the single stored proc if you're using a generated C# data access layer like LinqToSQL a single class is generated to represent your resultset.

Answer 6

I second @tvanfosson.

However, I would add that you can do both: have a multi-use sproc (e.g. GetX) which contains the essential logic for a whole class of queries, and wrap it up in a series of smaller sprocs (GetXY, GetXZ) which execute the big one, passing in the appropriate parameters.

This means that you Don't Repeat Yourself, but you can also provide a simple interface to the client apps: an app which only ever calls GetXY doesn't have to know about GetXZ.

We use this approach sometimes.

Answer 7

AJs approach gives you the best of both worlds. The pain of having to maintain repeated code across several sprocs cannot be overstated.

Build Sproc and UDF modules for common use, and call them from task-specific sprocs.