I'm actually arguing against this, but want to see if I'm out of line.

We have a PaymentQueue table and a simple UI. This table is, at the moment, primarily used by the UI and no other business logic. The UI requires data that is only in a subset of possible statuses.

One developer put this status restriction in the SPROC itself. I'm arguing to put it into the website UI layer, since it's view dependent.

The benefit if his approach is it returns less data over the wire, but messes up other processes that may, in the future, utilize this SP, since it now always restricts to certain types. The benefit of my approach is it keeps view logic out of the database, but pulls across more data on the wire (I plan to narrow by date range to minimize this).

My thought was to introduce a flag in the SPROC that, if enabled, would filter by these types, thus allowing the SPROC to be more flexible for either case.

What is the best approach? Thanks.

  • 2
    If your software requirement is that the data types be flexible, then a single SPROC that limits the data types is not going to work. Using a flag sounds like a hack. Jan 6, 2014 at 16:50
  • 1
    Why not develop a new sproc when requirements dictate? The best thing to do is cut down the traffic, and create sprocs that solve exactly the problem at hand. Jan 8, 2014 at 5:22

1 Answer 1


Creating a SP (or better, a View) that returns a subset of data from a table is workable, but overloading the SP with a flag to allow it to be used again in a different way isn't a good option. The key decisions revolve around deciding where the business logic for filtering the returned data should exist; and weighing the trade-offs between code reuse, readability, and maintainability. The worst thing that you can do is to duplicate the logic in both places, because that make maintenance and issue resolution hard when the rules get out of sync which they inevitably will. I will outline four general options, though there are surely others.

  1. At one extreme, you let all the logic live in the database (which it sounds like your DBAs would like) and make calls and get back data through program specific SPROCs. It should make your job as a UI developer easier, as you can ignore the underlying schema and treat the SPROCs as a an API for the data. Just remember that the DBA will need the same set of good requirements and acceptance criteria that you need to succeed. The down side to this is that you can get overrun with SPROCs if the database needs to support multiple systems with different needs. Microsoft SQL Server introduced Schemas as part of SQL Server 2005 to help manage this, but it is still an added complexity. Each UI is tightly coupled to its own set of stored procedures so deployment of changes isn't a problem as the SPROCs are isolated from each other (by Schema or naming convention).

  2. In the middle, you can create generic SPROCs that return all data of a given type and filter in the consuming program. This is the safest, as it keeps the filter logic isolated to the programs that need it and reduces most of the procedures to simple select statements with joins and no parameters. Using column names rather than ordinal indexes to access data in the results can even somewhat isolate programs from schema changes in the returned results. The down side here (as you noted above) is that additional data is returned for every call, which can impact performance.

  3. Another middle option is to build more complex SPROCs that take in parameters which aid in filtering the data. The stored procedures in this case can become quite complex. Also, the coupling between the front ends and database can cause deployment and maintenance issues, especially if you are needing to alter the parameter list on a shared stored procedure and all dependent programs are not on a synchronized release schedule.

  4. At the other extreme, you can let all of the logic live above the database and use an ORM to handle all access to the underlying data. If you are interested in using Linq to SQL as your ORM, look at https://stackoverflow.com/questions/1075540/linq-to-sql-how-to-do-where-column-in-list-of-values and see how they are passing a list of values into a method. You could easily pass a list of statuses from the UI into a method and let the Linq to SQL generate appropriate SQL for you. Think about the responsibilities of your tiers and don't be afraid to have helper methods that call GetPaymentList with the appropriate status lists to hide that complexity.

My order of preference is 1, 4, 2, 3. If (as you state in a comment) you are forced to SPROC by your DBAs, I would suggest going with the first option and building SPROCs specific for your application. This assumes that you either have the DBAs writing the stored procedures for you, or you have SQL talent on your team. If neither assumption holds, fall back to the first middle ground with generic SPROCs and filter in the UI. The third option (trying to build a flexible set of stored procedures that can work for any possible future use) is over-engineering. Rather than solving a problem that doesn't exist yet, I would much rather see you spend that extra time building a good set of regression tests for the code/interface so that you can refactor it with confidence when the problem (or some other requirement) arises.

The fourth option won't work for you because of the DBA requirement, but is also a very valid alternative for use in a different environment.

  • We are using SPROCs, as this is a requirement of our DBAs. My concern was limiting data that we pull across the wire, which is why I figured a flag might be a middle-ground. One other thought I just had, was perhaps using a custom data type (array) to allow my DAL to pass in a set of statuses, that the SP will handle and return. This would keep the SP flexible to allow me to get any number of statuses, if provided, and allows the application to dictate what it gets. Thoughts on this? Jan 7, 2014 at 15:23
  • 1
    Updated the answer to be a bit more generic, and outline the pros and cons of the several options. You can (and I have) build stored procedures that take in arrays and use them to filter, but it gets complex quickly and really doesn't buy you that much flexibility in the long run. In the past, I have usually ended up building new stored procedures anyway because the new program needs another column, or a join to another table, or another filter...you get the point. Keep it simple, build a SP that returns just what you need for now and refactor later. Decouple your layers. Jan 8, 2014 at 5:19
  • Agreed with this point. While I did end up passing in an array type and filtering on that, I'll keep in mind if I need swap it out for a dedicated SP that returns a more specified set of data. I didn't prefer #1, as I want to leverage our middle-tier where possible, I didn't prefer #2 due to the performance reasons, I like #3 (and am currently rolling with that one), and cannot use #4 due to the SPROC policy set by our DBAs. Jan 8, 2014 at 12:07
  • I have used #3 successfully in the past, it is certainly a valid choice and you know your domain better than anyone external can. The downsides are all manageable with some forethought and might not even be that bad if your schema isn't complex. Good luck, let us know how it works out! Jan 8, 2014 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.