The sp_executesql procedure

The sp_executesql procedure is the recommended method for sending an ad hoc T-SQL statement to SQL Server. If stored procedures cannot be leveraged for some reason, such as when T-SQL statements must be constructed dynamically by the application, sp_executesql allows the user to send an ad hoc T-SQL statement as a parameterized query, which uses a similar caching mechanism to stored procedures. This ensures that the plan can be reused whenever the same query is executed again. Building on our example from the Ad hoc plan caching section, we can rewrite the query using sp_executesql, as in the following example which can be executed in the AdventureWorks sample database:

EXECUTE sp_executesql @stmt = N'SELECT LastName, FirstName, MiddleName
FROM Person.Person
WHERE PersonType = @PersonType;'
                     , @params = N'@PersonType nchar(2)'
                     , @PersonType = N'EM';

This ensures that any time the same query is sent with the same parameter markers, the plan will be reused, even if the statement is dynamically generated by the application.