SQL Views and Sprocs

Question

I've made it a company policy that all data access has to be done via Sprocs. That includes Selects, Updates, Deletes and Inserts. We're starting to become overrun by them. Am I overkilling it? The selects are being executed, and the results dumped into a custom DAL.

I have a feeling that this is a holy war kind of question. I'm leaning to side that the sprocs are overkill for simple selects, but a necessity for updates, inserts, and deletes. One of the arguments for sprocs is security. Certain users aren't supposed to have access to certain parts of the app. Who here actually creates more than one user on a db for a website? No hands?

I also have a need for retrieving stats about a number of tables. It's one query with a number of calls to UDF's. These stats change by the minute. Should I use a view or a sproc? I'm learning towards a sproc, as the view would just have to rebuild itself every time anyway. Is this a correct assumption?

Answer 1

In addition to stored procedures, you could allow access to views. You can still control access to views, but one view can replace many stored procedures.

You can also group stored procedures by functionality, and use a prefix per group. Like, cart_AddItem, or user_GetPreferences. This is a lot of work to implement retroactively, but it's never too late to start.

If you're maintaining a database that is accessed by multiple programs and/or websites, stored procedures are the only way I know of to keep complexity manageable. On the other hand, if it's just a single website, a LINQ like data access layer can work very well.

Answer 2

OK, this is going to make me sound like I'm being a jerk, but I really don't mean to be. My answer to your question is another question.

"What business do you have defining development policy if you don't understand how the database works?"

You say "the view would just have to rebuild itself every time anyway". Are you saying that any code with a view gets recompiled each time it's queried? Because that's absolutely not true, (at least on Oracle and SQL Server, that is). Using views is a far more flexible way to get efficient queries than stored procedures because the optimizer can re-optimize your view by how you use it. And once it has done so, the query plan is cached so it doesn't have to do a recompilation. Case in point: You create this view:

CREATE VIEW myOrders AS
SELECT c.CustomerID, c.LastName, c.FirstName, o.OrderID, o.OrderPrice
FROM Customers c
LEFT JOIN Orders o
   ON o.CustomerID = o.CustomerID;

You then decide that you want a list of all customers named John Smith:

SELECT c.CustomerID
FROM myOrders
WHERE c.LastName = 'Smith' AND c.FirstName = 'John';

Because it's a view, the join to "Orders" gets optimized away. Were you to try to modularize this in a stored procedure, well, you couldn't. You'd probably have to make another sproc just for the purpose. And pretty soon you end up with the problem you have, which is a ton of barely maintainable procs.

Why are you using stored procedures? What is the problem you're trying to solve? Encapsulation? I would argue that for SELECTs, UDFs can be more useful. I would also argue that LINQ-type queries on the middle tier are even more flexible. Are you trying to optimize performance by using stored procs? If so, know and understand that every ad-hoc query you run gets optimized and cached. If you're using SQL Server 2005+, you can even force it to parameterize and cache your query plans, even if you don't specify parameters explicitly.

Stored procedures have their place, but their drawbacks of maintainability, flexibility of use, and yes, even performance, means that you should use them judiciously, not create blanket policies for your developers to be hemmed in by.