I am going through the stored procedures written throughout our project and see that multiple distinct queries are written inside a single stored procedure and are called by passing different values to input parameter.
Will this not be a performance issue? If multiple clients are calling the same stored procedure by passing different values to the input parameter then the stored procedure execution plan will have to be recompiled each time it is called and degrade the performance?
The only reason I can comprehend is to save from writing new service methods to call different stored procedures and reuse existing service methods by just passing different parameters.
Does anyone see any other advantage of writing the stored procedure this way?
What could be the other disadvantages of writing it this way besides performance?