I am going through some code in an application that calls stored procedures on SQL Server (2014). In the C# code, it executes the procedure and passes in WITH RECOMPILE.
The procedures consist of a single SQL statement, some conditionals, and then some dynamic SQL executed with EXEC...
The procedures have NO parameters.
I know using recompile is useful when less than optimal plans are used but targeting specific SQL statements is preferred rather than the whole procedure - in general. The fact that there are no parameters at all is a concern and caching a plan for that is what a cached plan is for in the first place.
I did some tests with statistics time and compared the two calls using recompile and not using it. Using it adds to the parse and compile time - obviously. Not a lot when seen as an individual procedure call but when done possibly hundreds of times it does add up.
I was going to request that the use of recompile is removed from the client code because I don't see any need for it but wanted to make sure I have not missed anything.
All of my searches returned results for when procedures are called with parameters and that doesn't apply in this case.
Thanks in advance for your time!