When does a Stored Procedure recompile?(Sql Server)

Question

I guess,

When i use temporary table in Stored Procedure ,the stored procedure will be automatically recompiled.

Kindly give me the other possibilites.

Answer 1

It's all here: Execution Plan Caching and Reuse under the section "Recompiling Execution Plans"

Answer 2

Optimizing SQL Server Stored Procedures to Avoid Recompiles

• dropping and recreating the stored procedure
• using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement
• changing the schema of any referenced objects
• running the sp_recompile system stored procedure against a table referenced by the stored procedure
• restoring the database containing the stored procedure or any object referenced by the stored procedure
• the stored procedures plan dropping from the cache
• Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table.
• Stored procedures will recompile if the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the code.