When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, then the text of the stored procedure is stored in the syscomments system table.
When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.
In the resolution stage, Microsoft® SQL Server™ also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, or if other errors are found in the resolution stage, an error is returned and the stored procedure does not execute.
Note If an object referenced by a stored procedure is deleted or renamed, then an error is returned when the stored procedure is executed. However, if an object referenced in a stored procedure is replaced with an object of the same name, the stored procedure executes without having to be recompiled. For example, if stored procedure proc1 references table test1, and test1 is deleted and a different table called test1 is created, proc1 references the new table. The stored procedure does not have to be recompiled.
If procedure execution successfully passes the resolution stage, the query optimizer in SQL Server analyzes the Transact-SQL statements in the stored procedure and creates an execution plan. The execution plan describes the fastest method of executing the stored procedure, based on information such as:
After the query optimizer has analyzed these factors in the stored procedure, it will place the execution plan in memory. The process of analyzing the stored procedure and creating an execution plan is called compilation. The optimized in-memory execution plan will be used to execute the query. The execution plan will stay in memory until SQL Server is restarted, or until space is needed for storage of another object.
When the stored procedure is subsequently executed, SQL Server will reuse the existing execution plan if it is still in memory. If the execution plan is no longer in memory, a new execution plan is created. For more information, see Stored Procedure and Trigger Execution.
| CREATE PROCEDURE | Execution Plan Caching and Reuse |