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

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.

主站蜘蛛池模板: 芜湖县| 黄骅市| 清徐县| 仁布县| 蓝山县| 景泰县| 达州市| 乐山市| 鄂托克旗| 岑溪市| 新建县| 牙克石市| 乌鲁木齐县| 翼城县| 镇雄县| 略阳县| 浠水县| 手游| 柘城县| 古蔺县| 宿迁市| 溆浦县| 利津县| 邢台县| 余姚市| 沙坪坝区| 老河口市| 阜新| 齐齐哈尔市| 乐清市| 九龙城区| 安溪县| 西乌珠穆沁旗| 台江县| 双牌县| 卫辉市| 克山县| 垣曲县| 鹤庆县| 平潭县| 栾城县|