使用 sp_executesql 动态执行sql
参考: https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15
语法
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse sp_executesql [ @stmt = ] statement [ { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } { , [ @param1 = ] 'value1' [ ,...n ] } ]
sp_executesql 支持独立于 Transact-SQL 字符串设置参数值,如以下示例所示。
DECLARE @IntVariable int; DECLARE @SQLString nvarchar(500); DECLARE @ParmDefinition nvarchar(500); /* Build the SQL string one time.*/ SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID FROM AdventureWorks2012.HumanResources.Employee WHERE BusinessEntityID = @BusinessEntityID'; SET @ParmDefinition = N'@BusinessEntityID tinyint'; /* Execute the string with the first parameter value. */ SET @IntVariable = 197; EXECUTE sp_executesql @SQLString, @ParmDefinition, @BusinessEntityID = @IntVariable; /* Execute the same string with the second parameter value. */ SET @IntVariable = 109; EXECUTE sp_executesql @SQLString, @ParmDefinition, @BusinessEntityID = @IntVariable;
输出参数也可用于 sp_executesql。 以下示例从 AdventureWorks2012.HumanResources.Employee
表中检索职务,并在输出参数 @max_title
中返回结果。
DECLARE @IntVariable int; DECLARE @SQLString nvarchar(500); DECLARE @ParmDefinition nvarchar(500); DECLARE @max_title varchar(30); SET @IntVariable = 197; SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) FROM AdventureWorks2012.HumanResources.Employee WHERE BusinessEntityID = @level'; SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT'; EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT; SELECT @max_title;
替换 sp_executesql 中的参数的能力,与使用 EXECUTE 语句执行字符串相比,有下列优点:
-
因为在 sp_executesql 字符串中,Transact-SQL 语句的实际文本在两次执行之间并未改变,所以查询优化器应该能将第二次执行中的 Transact-SQL 语句与第一次执行时生成的执行计划匹配。 因此,SQL Server 不必编译第二条语句。
-
Transact-SQL 字符串只生成一次。
-
整数参数按其本身格式指定。 不需要转换为 Unicode。
权限
要求具有 public 角色的成员身份。
示例
A. 执行简单的 SELECT 语句
以下示例创建并执行一个简单的 SELECT
语句,其中包含名为 @level
的嵌入参数。
EXECUTE sp_executesql N'SELECT * FROM AdventureWorks2012.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
语句中。
CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT, @PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME AS DECLARE @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, @PrmDeliveryDate GO
在该过程中使用 sp_executesql 比使用 EXECUTE 执行字符串更有效。 使用 sp_executesql 时,只生成 12 个版本的 INSERT 字符串,每个月的表对应 1 个字符串。 使用 EXECUTE 时,因为参数值不同,每个 INSERT 字符串均是唯一的。 尽管两种方法生成的批处理数相同,但因为 sp_executesql 生成的 INSERT 字符串都相似,所以查询优化器更有可能重复使用执行计划。
C. 使用 OUTPUT 参数
下面的示例使用OUTPUT
用于存储生成的结果集参数SELECT
中的语句@SQLString
参数。两个SELECT
使用的值,然后将执行语句OUTPUT
参数。
USE AdventureWorks2012; GO DECLARE @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, TotalDue FROM Sales.SalesOrderHeader WHERE SalesOrderNumber = @SalesOrderNumber;
示例:Azure Synapse Analytics (SQL DW) 和 并行数据仓库
D. 执行简单的 SELECT 语句
以下示例创建并执行一个简单的 SELECT
语句,其中包含名为 @level
的嵌入参数。
-- Uses AdventureWorks EXECUTE sp_executesql N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee WHERE EmployeeKey = @level', N'@level tinyint', @level = 109;
本文来自博客园,作者:兴想事成,转载请注明原文链接:https://www.cnblogs.com/mjxxsc/p/11937510.html