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?
Best Answer
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
: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 ofRECOMPILE
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.