Happenstance
Wrapper stored procedures are useful for things like:
- Transforming declared local variables into parameters
- Preventing code from compiling when it isn’t used
- Generating different query plans to deal with parameter sniffing
The upside of using this over dynamic SQL is that you have a convenient object name attached to the code.
The downside is that if stored procedures share code logic, you now have more to maintain. Likewise, if you have many IF...ELSE branches, you’ll have many more stored procedures to dig around to (though they’ll be a lot easier to identify in the plan cache and query store).
There are other handy things about stored procedures that dynamic SQL makes iffy, like permissions, and making sure developers don’t write the kind of code that makes headlines.
Stored procedures also aren’t a good use case for all the “kitchen sink” queries that use a lot of optional parameters. Dynamic SQL is king here, because creating a stored procedure for every permutation of parameter combinations is a bigger no than well done steak.
To learn how to deal with those, watch these videos.
Thanks for reading (and watching)!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Thank you! I have used OPTION(OPTIMIZE FOR ()) a couple of times recently with clenched teeth because adding query hints is usually a questionable idea (in the case of OPTIMIZE FOR, what if the data distribution changes?). I will try using a wrapper stored procedure next time.
That depends a bit. Are you optimizing for unknown, or for a specific value?