When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL Server’s buffer. If the same stored procedure is called again from the same connection, the server will use the cached query plan instead of creating a new one, saving time and boosting performance. This may or may not be what you want. If a query is dynamic (the WHERE clauses change from one execution of the stored procedure to the next), it will not be optimized when it is run, and the performance of the query can suffer greatly.

If the query will vary each time it is run from the stored procedure, add the WITH RECOMPILE option when the stored procedure is created. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized each time.