Transact-SQL Tips: "Reusing Execution Plans"
Use parameterized queries, and supply the parameter values instead of specifying stored procedure parameter values or the values in search condition predicates directly. Use either the parameter substitution in sp_executesql or the parameter binding of the ADO, OLE DB, ODBC, and DB-Library APIs.
For example, do not code this SELECT:
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = 3
SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = ?
In a Transact-SQL script, stored procedure, or trigger, use sp_executesql to execute the SELECT statement:
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string. */
SET @SQLString =
N'SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = @ShipID'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@ShipID int'
/* Execute the string. */
SET @IntVariable = 3
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ShipID = @IntVariable
- Execution Plan Caching and Reuse
- Building Statements at Run Time
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx