官术网_书友最值得收藏!

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.

主站蜘蛛池模板: 甘肃省| 石泉县| 东海县| 阿拉善盟| 资阳市| 海城市| 三穗县| 海城市| 阜康市| 淳化县| 福安市| 星子县| 江源县| 石阡县| 资溪县| 西乌珠穆沁旗| 南平市| 秀山| 许昌市| 木里| 海城市| 延寿县| 望城县| 汉寿县| 盐津县| 黔西| 宕昌县| 康保县| 曲阜市| 六安市| 藁城市| 浪卡子县| 云南省| 利辛县| 崇文区| 石屏县| 垣曲县| 吴堡县| 苏尼特右旗| 洛南县| 称多县|