What is the meaning of "stored procedures are pre-compiled"?
They are actually pre-parsed and syntax/semantics checked on CREATE and ALTER
"Compilation" to a query plan happens on demand
For an overview of compilation and re-use, see "Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 "
The terminology (in what you mean) goes back to SQL Server 6.5. The "new" way highlighted in the previous white paper link started with SQL Server 7.0
In Microsoft SQL Server, stored procedures are compiled into a query plan the first time they are run.
At subsequent runs, they are sometimes recompiled from source, but not always. That is why they are called "pre-compiled".
Unless you specify that they should always be recompiled, or when you run sp_recompile
to enforce recompilation at the next run time.