[转载]sp_executesql 与 exec
来源:互联网 发布:eCryptFS centos 编辑:程序博客网 时间:2024/05/16 14:13
sp_executesql
执行可以多次重复使用或动态生成的 Transact-SQL 语句或批处理。Transact-SQL 语句或批处理可以包含嵌入参数。
语法:
包含 Transact-SQL 语句或批处理的 Unicode 字符串。statement 必须是 Unicode 常量或 Unicode 变量。 包含 stmt 中嵌入的所有参数定义的字符串。字符串必须是 Unicode 常量或 Unicode变量。 参数字符串中定义的第一个参数的值。该值可以是 Unicode 常量,也可以是 Unicode 变量。 指示参数是输出参数。除非是公共语言运行 (CLR) 过程,否则 text、ntext 和 image 参数均可用作 OUTPUT 参数。 附加参数值的占位符。这些值只能为常量或变量,不能是很复杂的表达式(例如函数)或使用运算符生成的表达式。 在批处理、名称作用域和数据库上下文方面,sp_executesql 与 EXECUTE 的行为相同。sp_executesql stmt 参数中的 Transact-SQL 语句或批处理在执行 sp_executesql 语句时才编译。随后,将编译 stmt 中的内容,并将其作为执行计划运行。该执行计划独立于名为 sp_executesql 的批处理的执行计划。sp_executesql 批处理不能引用调用 sp_executesql 的批处理中声明的变量。sp_executesql 批处理中的本地游标或变量对调用 sp_executesql 的批处理是不可见的。对数据库上下文所做的更改只在 sp_executesql 语句结束前有效。 如果只更改了语句中的参数值,则 sp_executesql 可用来代替存储过程多次执行 Transact-SQL 语句。因为 Transact-SQL 语句本身保持不变,仅参数值发生变化,所以 SQL Server 查询优化器可能重复使用首次执行时所生成的执行计划。 以下示例将创建并执行一个简单的 以下示例显示使用 这些表用于划分一年的销售数据。一年中的每个月均有一个表,格式如下: 此示例存储过程将动态生成并执行 在该过程中使用 sp_executesql 比使用 EXECUTE 执行字符串更有效。使用 sp_executesql 时,只生成 12 个版本的 INSERT 字符串,每个月的表对应 1 个字符串。使用 EXECUTE 时,因为参数值不同,每个 INSERT 字符串均是唯一的。尽管两种方法生成的批处理数相同,但因为 sp_executesql 生成的 INSERT 字符串都相似,所以查询优化器更有可能重复使用执行计划。 以下示例使用 sp_executesql 和 EXECUTE 执行一段SQL 语句,有时候我们必须借助它来完成某些SQL语句,比如需要将数据库名或表名作为参数的场合。 通过执行下列两个批处理来举例说明: 如果语句重复执行,则即使仅有的区别是为参数所提供的值不同,每次执行时也必须生成全新的 Transact-SQL 字符串。从而在下面几个方面产生额外的开销: sp_executesql 支持与 Transact-SQL 字符串相独立的参数值的设置: 此 sp_executesql 示例完成的任务与前面的 EXECUTE 示例所完成的相同,但有下列额外优点: 说明 为了使 SQL Server 重新使用执行计划,语句字符串中的对象名称必须完全符合要求。 在 SQL Server 早期的版本中要重新使用执行计划的唯一方式是,将 Transact-SQL 语句定义为存储过程然后使应用程序执行此存储过程。这就产生了管理应用程序的额外开销。使用 sp_executesql 有助于减少此开销,并使 SQL Server 得以重新使用执行计划。当要多次执行某个 Transact-SQL 语句,且唯一的变化是提供给该 Transact-SQL 语句的参数值时,可以使用 sp_executesql 来代替存储过程。因为 Transact-SQL 语句本身保持不变仅参数值变化,所以 SQL Server 查询优化器可能重复使用首次执行时所生成的执行计划。 下例为服务器上除四个系统数据库之外的每个数据库生成并执行 DBCC CHECKDB 语句: 当目前所执行的 Transact-SQL 语句包含绑定参数标记时,SQL Server ODBC 驱动程序使用 sp_executesql 完成 SQLExecDirect。但例外情况是 sp_executesql 不用于执行中的数据参数。这使得使用标准 ODBC 函数或使用在 ODBC 上定义的 API(如 RDO)的应用程序得以利用 sp_executesql 所提供的优势。定位于 SQL Server 2000 的现有的 ODBC 应用程序不需要重写就可以自动获得性能增益。有关更多信息,请参见使用语句参数。 用于 SQL Server 的 Microsoft OLE DB 提供程序也使用 sp_executesql 直接执行带有绑定参数的语句。使用 OLE DB 或 ADO 的应用程序不必重写就可以获得 sp_executesql 所提供的优势。 sp_executesql [ @statement = ] statement[ { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } { , [ @param1 = ] 'value1' [ ,...n ] }]
A. 执行简单的 SELECT 语句
SELECT
语句,其中包含名为 @level
的嵌入参数。 N'SELECT * FROM AdventureWorks2008R2.HumanResources.Employee WHERE BusinessEntityID = @level', N'@level tinyint', @level = 109;
B. 执行动态生成的字符串
sp_executesql
执行动态生成的字符串。该示例中的存储过程用于向一组表中插入数据,CREATE TABLE May1998Sales (OrderID int PRIMARY KEY, CustomerID int NOT NULL, OrderDate datetime NULL CHECK (DATEPART(yy, OrderDate) = 1998), OrderMonth int CHECK (OrderMonth = 5), DeliveryDate datetime NULL, CHECK (DATEPART(mm, OrderDate) = OrderMonth) )
INSERT
语句,以便向正确的表中插入新订单。此示例使用订货日期生成应包含数据的表的名称,然后将此名称并入 INSERT
语句中。 @PrmOrderDate DATETIME, @PrmDeliveryDate DATETIMEASDECLARE @InsertString NVARCHAR(500)DECLARE @OrderMonth INT-- Build the INSERT statement.SET @InsertString = 'INSERT INTO ' + /* Build the name of the table. */ SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) + CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) + 'Sales' + /* Build a VALUES clause. */ ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' + ' @InsOrdMonth, @InsDelDate)'/* Set the value to use for the order month because functions are not allowed in the sp_executesql parameter list. */SET @OrderMonth = DATEPART(mm, @PrmOrderDate)EXEC sp_executesql @InsertString, N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME, @InsOrdMonth INT, @InsDelDate DATETIME', @PrmOrderID, @PrmCustomerID, @PrmOrderDate, @OrderMonth, @PrmDeliveryDateGO
C. 使用 OUTPUT 参数
OUTPUT
参数将由 SELECT
语句生成的结果集存储于 @SQLString
参数中。
然后将执行两个使用 OUTPUT
参数值的 SELECT
语句。USE AdventureWorks2008R2;GODECLARE @SQLString nvarchar(500);DECLARE @ParmDefinition nvarchar(500);DECLARE @SalesOrderNumber nvarchar(25);DECLARE @IntVariable int;SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber) FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID';SET @ParmDefinition = N'@CustomerID int, @SalesOrderOUT nvarchar(25) OUTPUT';SET @IntVariable = 22276;EXECUTE sp_executesql @SQLString ,@ParmDefinition ,@CustomerID = @IntVariable ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;-- This SELECT statement returns the value of the OUTPUT parameter.SELECT @SalesOrderNumber;-- This SELECT statement uses the value of the OUTPUT parameter in-- the WHERE clause.SELECT OrderDate, TotalDueFROM Sales.SalesOrderHeaderWHERE SalesOrderNumber = @SalesOrderNumber;
关于这2个命令需要注意的地方:/* Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3)
SET @CharVariable = 'abc'
/* sp_executesql fails because @CharVariable has gone out of scope. */
sp_executesql N'PRINT @CharVariable'
GO
/* Show database context resetting after sp_executesql completes. */
USE pubs
GO
sp_executesql N'USE Northwind'
GO
/* This statement fails because the database context
has now returned to pubs. */
SELECT * FROM Shippers
GO
参数的传递
sp_executesql 支持对 Transact-SQL 字符串中指定的任何参数的参数值进行替换,但是 EXECUTE 语句不支持。
因此,由 sp_executesql 生成的 Transact-SQL 字符串比由 EXECUTE 语句所生成的更相似。
SQL Server 查询优化器可能将来自 sp_executesql 的 Transact-SQL 语句与以前所执行的语句的执行计划相匹配,
以节约编译新的执行计划的开销。使用 EXECUTE 语句时,必须将所有参数值转换为字符或 Unicode 并使其成为 Transact-SQL 字符串的一部分:
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
/* Build and execute a string with one parameter value. */
SET @IntVariable = 35
SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +
CAST(@IntVariable AS NVARCHAR(10))
EXEC(@SQLString)
/* Build and execute a string with a second parameter value. */
SET @IntVariable = 201
SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +
CAST(@IntVariable AS NVARCHAR(10))
EXEC(@SQLString)
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string once. */
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
/* Specify the parameter format once. */
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
重新使用执行计划
USE master
GO
SET NOCOUNT ON
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sysdatabases WHERE dbid > 4
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'CHECKING DATABASE ' + @DBNameVar
SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
+ N'DBCC CHECKDB (' + @DBNameVar + N')'
EXEC sp_executesql @Statement
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF
GO
- [转载]sp_executesql 与 exec
- exec与sp_executesql
- exec与sp_executesql
- exec与sp_executesql的区别
- mssql:sp_executesql与exec(@sql)
- exec与 exec "sp_executesql" 的用法
- EXEC sp_executesql
- EXEC sp_executesql
- SQLSERVER 动态执行SQL sp_executesql与EXEC
- SQLSERVER 动态执行SQL sp_executesql与EXEC
- exec与sp_executesql语法的区别详解
- EXEC与sp_executesql的区别及应用
- exec与sp_executesql语法的区别详解
- SQLSERVER 动态执行SQL sp_executesql与EXEC
- SQLSERVER的自定义函数,不支持使用exec sp_executesql与Exec()
- exec 与 exec sp_executesql 的用法及比较
- 动态语句语法:exec/sp_executesql语法(转载)
- 动态语句语法:exec/sp_executesql语法(转载)
- 长城——Beyond
- java定时执行
- kettle应用实践
- 测试1
- sizeof总结
- [转载]sp_executesql 与 exec
- U-boot-2009.03移植之十二:第二阶段——支持128M Nandflash
- Ubuntu Server 下开启远程连接 MySQL
- [flash疯狂破解加密系列一]使用as3+alchemy对swf自我加密
- [flash疯狂破解加密系列二]疯狂的加壳方法
- 企业常用网管软件介绍及配置说明
- 强制断开oracle数据库的用户连接
- [flash疯狂破解加密系列三]内存抓取法破解swf加壳
- 调试工具xdebug的配置说明