Stored Procedure Consolidation

Question

I recently started working on a project using stored procedures from data vs. ORM's

I haven't seen this project's procedure layout before and was wondering if it's ok / what would be better.

Code would similar to this

procedure getCarsBy

@color
@inProduction
@queryType

case queryType = 1
    Select from Cars where color = @color
case queryType = 2
    Select from Cars where inProduction = @inProduction

Most the procedures have big case blocks for each where clause configuration.

I'd like to break things out of the case statements because it doesn't seem proper to duplicate the select piece all over the place but I'm not sure if dynamic sql or many procedures would be better.

For example getCarsByColor, getCarsByInProduction ect.

Thoughts?

Answer 1

Your question may be a candidate for closing as "Primarily opinion-based" but yes, I'd follow your proposed pattern, to a point. What I tend toward is identifying the most frequent parameter combinations and coding discrete procedures for them.

It becomes unfeasible with a large number of combinations of parameters to code and maintain for each, although you could code-gen them. Instead, deal with the lesser combinations with dynamic SQL, or static SQL with OPTION RECOMPILE:

SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
       c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
       c.PostalCode, c.Country, c.Phone, p.ProductID,
       p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM   Orders o
JOIN   [Order Details] od ON o.OrderID = od.OrderID
JOIN   Customers c ON o.CustomerID = c.CustomerID
JOIN   Products p ON p.ProductID = od.ProductID
WHERE  (o.OrderID = @orderid OR @orderid IS NULL)
  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)
  AND  (o.OrderDate <= @todate OR @todate IS NULL)
  AND  (od.UnitPrice >= @minprice OR @minprice IS NULL)
  AND  (od.UnitPrice <= @maxprice OR @maxprice IS NULL)
  AND  (o.CustomerID = @custid OR @custid IS NULL)
  AND  (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)
  AND  (c.City = @city OR @city IS NULL)
  AND  (c.Region = @region OR @region IS NULL)
  AND  (c.Country = @country OR @country IS NULL)
  AND  (od.ProductID = @prodid OR @prodid IS NULL)
  AND  (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)
ORDER  BY o.OrderID
OPTION (RECOMPILE)

The effect of all the @x IS NULL clauses is that if that input parameter is NULL, then that AND-condition is always true. Thus, the only conditions that are in effect are those where the search parameter has a non-NULL value.

As far as maintainability goes, it's difficult to think of a better solution for the search conditions at hand. It's compact, easy to read and to extend. And performance? Very good as long as you include the query hint OPTION (RECOMPILE). This hint forces the query to be recompiled each time, in which case SQL Server will use the actual variable values as if they were constants.

Query and quote courtesy of Erland Sommarskog's reference article on the topic, Dynamic Search Conditions.

By coding specifically for the common cases you reduce the overhead of RECOMPILE incurred on the less frequent cases. That said, I confess I'm getting good value from more liberal application of RECOMPILE recently. In the era of more cores & hyper-threading, the recompilation overhead can (but certainly not always!) be preferable to one-size-fits-all execution plans.

Answer 2

With a single stored procedure (SP) you will run into parameter sniffing problems. RECOMPILE becomes mandatory. Every invocation now has a compilation overhead. This can mount up since the SP is, almost by definition, very large. Lots of small SPs are less likely to need OPTION (RECOMILE) individually and the cost of compiling less when it does happen.

The corner-case query Mark quotes is viable option. Be careful it does not become so complex that the optimiser hits compilation timeout limits.

A problem I've had previously with "one file to rule them all" solutions is developer contention. Since all changes have to be implemented in the one module, the team spends a lot of time merging individuals' updates into the master version. Of course this will vary with team size and functional volatility.