Auto-Parameterization
In Microsoft® SQL Server™ 2000, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, unused execution plans. If an SQL statement is executed without parameters, SQL Server 2000 parameterizes the statement internally to increase the possibility of matching it against an existing execution plan.
Consider this statement:
SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 1
The value 1 at the end of the statement can be specified as a parameter. The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. Because of this auto-parameterization, SQL Server 2000 recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:
SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 1
SELECT * FROM Northwind.dbo.Products WHERE CategoryID = 4
When processing complex SQL statements, the relational engine may have difficulty determining which expressions can be auto-parameterized. To increase the ability of the relational engine to match complex SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers. For more information, see Parameters and Execution Plan Reuse.