Erik Darling offers some advice:
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.
Read on for the downside to this, as well as a pair of videos on the topic.