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.