使用 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;
最新文章
- AnjularJS系列6 —— 过滤器
- C#可用的日出日落时间类
- [PHP]基本排序(冒泡排序、快速排序、选择排序、插入排序、二分法排序)
- template.js遍历对象的写法
- 如果简单的记录,就可以为这个世界创造更多的财富,那么还有什么理由不去写博客呢? — 读<;<;黑客与画家>;>; 有感
- python 循环定时器
- android 技术相关Blog
- 使用Maven创建一个Spring MVC Web 项目
- Linux Terminal命令
- jquery prop and attr
- Swift的闭包(二):捕获值
- kafka---broker 保存消息
- 计算机网络之iframe内联框架跨域
- myeclipse中的classpath .
- 记录一份Oracle 正确的监听配置文件listener.ora与tnsnames.ora
- Nest.js 处理错误
- Nginx查看并发链接数
- torchvision.datasets.ImageFolder数据加载
- 做了 3 年企业级 SaaS,我收获的 10 点心得(转)
- python调用虹软2.0