如果SQL Server执行动态SQL，应该如何实现？下面将为您介绍SQL Server执行动态SQL的两种正确方式。我希望它能让您在 SQL Server 上执行动态 SQL。对动态SQL有更深入的了解
动态SQL：动态执行的代码。它一般是根据用户输入或外部条件动态组合的SQL语句块。动态SQL可以灵活发挥SQL的强大功能，方便地解决一些其他方法难以解决的问题。相信使用过动态SQL的人都能体会到它带来的便利。但动态SQL有时在执行性能（效率）上不如静态SQL，而且如果使用不当，往往会存在安全隐患（SQL注入攻击）。
动态 SQL 可以通过两种方式执行：EXECUTE 或 SP_EXECUTESQL。
执行
执行命令字符串、Transact-SQL 批处理中的字符串，或执行以下模块之一：系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。 SQL Server 2005 扩展了 EXECUTE 语句，以便它可以用于向链接服务器发送传递命令。此外，还可以显式设置执行字符串或命令的上下文
SP_EXECUTESQL
执行可多次重用或动态生成的 Transact-SQL 语句或批处理。 Transact-SQL 语句或批处理可以包含嵌入参数。 SP_EXECUTESQL 与 EXECUTE 在批处理、名称范围和数据库上下文方面的行为相同。在执行 SP_EXECUTESQL 语句之前，不会编译 SP_EXECUTESQL stmt 参数中的 Transact-SQL 语句或批处理。随后，stmt 的内容被编译并作为执行计划运行。该执行计划独立于名为 SP_EXECUTESQL 的批处理执行计划。 SP_EXECUTESQL 批处理无法引用在调用 SP_EXECUTESQL 的批处理中声明的变量。 SP_EXECUTESQL 批处理中的本地游标或变量对于调用 SP_EXECUTESQL 的批处理不可见。仅在 SP_EXECUTESQL 语句末尾才对数据库上下文进行更改。至今有效。
如果仅更改语句中的参数值，可以使用sp_executesql代替存储过程多次执行Transact-SQL语句。由于 Transact-SQL 语句本身保持不变，只有参数值发生变化，因此 SQL Server 查询优化器可以重用第一次执行时生成的执行计划。
一般来说，我们推荐并优先使用SP_EXECUTESQL来执行动态SQL。一方面，它更加灵活，可以有输入和输出参数。另一方面，查询优化器更有可能重用执行计划，提高执行效率。也可以，使用SP_EXECUTESQL可以提高安全性；当然，这并不意味着EXECUTE应该被完全放弃。在某些情况下，EXECUTE 比 SP_EXECUTESQL 更方便。例如，动态 SQL 字符串是 VARCHAR 类型，而不是 NVARCHAR 类型。 SP_EXECUTESQL只能执行Unicode字符串或可以隐式转换为ntext的常量或变量，而EXECUTE可以执行这两种类型的字符串。
我们来比较一下EXECUTE和SP_EXECUTESQL的一些细节。
EXECUTE(N’SELECT * FROM Groups’) – 执行成功
EXECUTE(‘SELECT * FROM Groups’) – 执行成功
SP_EXECUTESQL N'SELECT * FROM Groups'; – 执行成功
SP_EXECUTESQL ‘SELECT * FROM Groups’ – 执行错误
总结：EXECUTE可以执行非Unicode或Unicode类型的字符串常量和变量。 SP_EXECUTESQL 只能执行 Unicode 或可隐式转换为 ntext 的字符串常量和变量。
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
EXECUTE(‘SELECT * FROM Groups WHERE GroupName=”’ + SUBSTRING(@GroupName, 1,5) + ””); – ‘SUBSTRING’附近有语法错误。
声明@Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
SET@Sql='SELECT * FROM Groups WHERE GroupName=”' + SUBSTRING(@GroupName, 1,5) + ””
–打印@Sql;执行(@Sql);
总结：EXECUTE 括号中只能包含字符串变量、字符串常量或其组合，不能调用其他函数、存储过程等。如果要使用，请使用变量组合，如上所示。
声明@Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
SET@Sql='从组中选择* WHEREGroupName=@GroupName'
–打印@Sql;执行(@Sql); –错误：必须声明标量变量“@GroupName”。SET@Sql='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, ””)
执行(@Sql); – 正确：
声明 @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';
SET@Sql='从组中选择* WHEREGroupName=@GroupName'
打印@Sql;
EXEC SP_EXECUTESQL @Sql,N'@GroupNameNVARCHAR',@GroupName
查询无结果，未声明参数长度。
声明 @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';
SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'
打印@Sql;
EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)',@GroupName
总结：动态批处理无法访问批处理中定义的局部变量。 SP_EXECUTESQL可以有输入和输出参数，比EXECUTE更灵活。
我们来看看EXECUTE和SP_EXECUTESQL的执行效率。首先清除缓存执行计划，然后更改@GroupName值，分别执行SuperAdmin、CommonUser、CommonAdmin 3次。然后看缓存信息
DBCC FREEPROCCACHE；
声明@Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin'; –‘CommonUser’、‘CommonAdmin’
SET@Sql ='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, ””)
执行(@Sql); SELECTcacheobjtype、objtype、usecounts、sql
来自 sys.syscacheobjects
SQL 不喜欢“%cache%”
ANDsql NOTLIKE ‘%sys.%’;
依葫芦画葫芦，接着我们看看SP_EXECUTESQL的执行效率
DBCC FREEPROCCACHE；
声明 @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin'; –‘CommonUser’、‘CommonAdmin’
SET@Sql ='从组中选择 * WHERE GroupName=@GroupName'
EXECUTESP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;
SELECTcacheobjtype、objtype、usecounts、sql
来自 sys.syscacheobjects
SQL 不喜欢“%cache%”
ANDsql NOTLIKE ‘%sys.%’;
总结：EXEC 生成了 3 个独立的 ad hoc 执行计划，而使用 SP_EXECUTESQL 只生成了 1 个执行计划并重复使用了 3 次。想象一下，如果一个库中有很多类似的动态SQL，并且它们被频繁执行。如果使用 SP_EXECUTESQL，可以提高性能。
以下是其他网友补充
由于一些特殊原因，我们需要在SQL语句或存储过程中动态创建SQL语句，然后在SQL语句或存储过程中动态执行。
这里，微软提供了两种方法，一种是使用
执行功能
执行方法是
execute(@sql)动态执行一条SQL语句，但是这里的SQL语句无法得到返回结果。还有一个方法
使用存储过程sp_ExecuteSql
使用该存储过程，可以返回动态语句中的参数。
例如
声明@sql nvarchar(800),@dd varchar(20)
set @sql=’set @mm=”测试字符串”’
exec sp_executesql @sql,N'@mm varchar(20) 输出',@dd 输出
选择@dd
执行它会将内部创建的SQL语句中的变量值返回给外部调用者。
主要来自工作中的偶然需要：
创建proc proc_InToServer @收费站点编号varchar(4),@车道编号tinyint,@进入时间varchar(23),@UID char(16),
@车牌 varchar(12),@车型 char(1),@识别车牌号 varchar(12),@识别车型 char(1),@充值金额,@交易状态 char(1),
@有图像位、@离开时间varchar(23)、@速度float、@HasInsert int输出
作为
开始
声明@inTime datetime、@TableName varchar(255)、@leaveTime datetime、@HasTable bit、@Sql nvarchar(4000)
选择@intime=转换(日期时间,@进入时间),@leaveTime=转换(日期时间,@离开时间)
set @TableName='ETC03_01_OBE 原传记录表_'+dbo.formatDatetime(@intime,'YYYYMMDD')
从 sysobjects 中选择 @HasTable=(Case when Count(*)>0 then 1 else 0 end)，其中 id=Object_id(@TableName) 且 ObjectProperty(id,'IsUserTable')=1
如果@HasTable=0
开始
set @Sql='CREATE TABLE [dbo].['+@TableName+'] (
[收费站点编号] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[车道号] [tinyint] NOT NULL,
[输入时间] [日期时间] NOT NULL,
[UID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL,
[车牌] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[车型] [字符] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[识别车牌号] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[识别车型] [字符] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[充值金额][金额]NULL,
[交易状态] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[有图像] [位] NOT NULL ,
[出发时间] [日期时间] NULL ,
[速度] [浮动] NULL,
约束’+’PK_’+@TableName+’主键（收费站号、车道号、进入时间、UID）
) 在 [小学]’
执行(@Sql)
完
set @sql = 'select @Cnt=count(*) from '+@TableName+ ' where 收费站号码=”'+@收费站号码+”' 车道号码='+cast(@车道号码 as varchar(4) ) +' 且入场时间=”'+@入场时间+”' 且UID=”'+@UID+””
设置@sql = @sql + ‘ if @Cnt=0 ‘
set @sql=@sql+'insert '+@TableName+'values("'+@充电站点编号+"','+cast(@lane number as varchar(4))+',"'+@输入时间+ "',"'+@Uid+"',"'+@车牌+
"',"'+@model+"',"'+ @identify 车牌号+"',"'+@identify 型号+"','+Cast(@charge amount as varchar(8))+'," '+ @交易状态+"','+cast(@有image as varchar(1))+
‘,”’+@出发时间+”’,’+Cast(@speed as varchar(8))+’)’
–执行(@sql)
exec sp_executesql @sql,N'@Cnt int output',@HasInsert 输出
结束
补充信息2、
SQL Server 在循环中执行动态 SQL 语句。
使用 Navicate 工具成功执行查询。
声明@name nvarchar(100)
声明@sql nvarchar(200)
声明@i int
设置@i =10000
同时@i<=99999
开始
设置@name = ‘测试’ +强制转换(@i as varchar(20))
set @sql =N'SELECT * INTO '+ @name +' FROM test'
exec sp_executesql @sql
打印@名字
设置@i=@i + 1
结束
如果SQL Server执行动态SQL，应该如何实现？下面将为您介绍SQL Server执行动态SQL的两种正确方式。希望能让您更深入地了解SQL Server执行动态SQL
动态SQL：动态执行的代码。它一般是根据用户输入或外部条件动态组合的SQL语句块。动态SQL可以灵活发挥SQL的强大功能，方便地解决一些其他方法难以解决的问题。相信使用过动态SQL的人都能体会到它带来的便利。但动态SQL有时在执行性能（效率）上不如静态SQL，而且如果使用不当，往往会存在安全隐患（SQL注入攻击）。
动态 SQL 可以通过两种方式执行：EXECUTE 或 SP_EXECUTESQL。
执行
执行命令字符串、Transact-SQL 批处理中的字符串，或执行以下模块之一：系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程。 SQL Server 2005 扩展了 EXECUTE 语句，以便它可以用于向链接服务器发送传递命令。此外，还可以显式设置执行字符串或命令的上下文
SP_EXECUTESQL
执行可多次重用或动态生成的 Transact-SQL 语句或批处理。 Transact-SQL 语句或批处理可以包含嵌入参数。 SP_EXECUTESQL 与 EXECUTE 在批处理、名称范围和数据库上下文方面的行为相同。在执行 SP_EXECUTESQL 语句之前，不会编译 SP_EXECUTESQL stmt 参数中的 Transact-SQL 语句或批处理。随后，stmt 的内容被编译并作为执行计划运行。该执行计划独立于名为 SP_EXECUTESQL 的批处理执行计划。 SP_EXECUTESQL 批处理无法引用在调用 SP_EXECUTESQL 的批处理中声明的变量。 SP_EXECUTESQL 批处理中的本地游标或变量对于调用 SP_EXECUTESQL 的批处理不可见。仅在 SP_EXECUTESQL 语句末尾才对数据库上下文进行更改。至今有效。
如果仅更改语句中的参数值，可以使用sp_executesql代替存储过程多次执行Transact-SQL语句。由于 Transact-SQL 语句本身保持不变，只有参数值发生变化，因此 SQL Server 查询优化器可以重用第一次执行时生成的执行计划。
一般来说，我们推荐并优先使用SP_EXECUTESQL来执行动态SQL。一方面，它更加灵活，可以有输入和输出参数。另一方面，查询优化器更有可能重用执行计划，提高执行效率。也可以，使用SP_EXECUTESQL可以提高安全性；当然，这并不意味着EXECUTE应该被完全放弃。在某些情况下，EXECUTE 比 SP_EXECUTESQL 更方便。例如，动态 SQL 字符串是 VARCHAR 类型，而不是 NVARCHAR 类型。 SP_EXECUTESQL只能执行Unicode字符串或可以隐式转换为ntext的常量或变量，而EXECUTE可以执行这两种类型的字符串。
我们来比较一下EXECUTE和SP_EXECUTESQL的一些细节。
EXECUTE(N’SELECT * FROM Groups’) – 执行成功
EXECUTE(‘SELECT * FROM Groups’) – 执行成功
SP_EXECUTESQL N'SELECT * FROM Groups'; – 执行成功
SP_EXECUTESQL ‘SELECT * FROM Groups’ – 执行错误
总结：EXECUTE可以执行非Unicode或Unicode类型的字符串常量和变量。 SP_EXECUTESQL 只能执行 Unicode 或可隐式转换为 ntext 的字符串常量和变量。
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
EXECUTE(‘SELECT * FROM Groups WHERE GroupName=”’ + SUBSTRING(@GroupName, 1,5) + ””); – ‘SUBSTRING’附近有语法错误。
声明@Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
SET@Sql='SELECT * FROM Groups WHERE GroupName=”' + SUBSTRING(@GroupName, 1,5) + ””
–打印@Sql;执行(@Sql);
总结：EXECUTE 括号中只能包含字符串变量、字符串常量或者它们的连接组合，不能调用其他函数、存储过程等。如果要使用就使用变量组合，如上所示。
声明@Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';
SET@Sql='从组中选择* WHEREGroupName=@GroupName'
–打印@Sql;执行(@Sql); –错误：必须声明标量变量“@GroupName”。SET@Sql='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, ””)
执行(@Sql); – 正确：
声明 @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';
SET@Sql='从组中选择* WHEREGroupName=@GroupName'
打印@Sql;
EXEC SP_EXECUTESQL @Sql,N’@GroupNameNVARCHAR’,@GroupName
查询无结果，未声明参数长度。
声明 @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin';
SET@Sql ='SELECT * FROM Groups WHERE GroupName=@GroupName'
打印@Sql;
EXEC SP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)',@GroupName
总结：动态批处理无法访问批处理中定义的局部变量。 SP_EXECUTESQL可以有输入和输出参数，比EXECUTE更灵活。
我们来看看EXECUTE和SP_EXECUTESQL的执行效率。首先清除缓存执行计划，然后更改@GroupName值，分别执行SuperAdmin、CommonUser、CommonAdmin 3次。然后看缓存信息
DBCC FREEPROCCACHE；
声明@Sql VARCHAR(200);
DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin'; –‘CommonUser’、‘CommonAdmin’
SET@Sql ='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, ””)
执行(@Sql); SELECTcacheobjtype、objtype、usecounts、sql
来自 sys.syscacheobjects
SQL 不喜欢“%cache%”
ANDsql NOTLIKE ‘%sys.%’;
依葫芦画葫芦，接着我们看看SP_EXECUTESQL的执行效率
DBCC FREEPROCCACHE；
声明 @Sql NVARCHAR(200);
DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin'; –‘CommonUser’、‘CommonAdmin’
SET@Sql ='从组中选择 * WHERE GroupName=@GroupName'
EXECUTESP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50)', @GroupName;
SELECTcacheobjtype、objtype、usecounts、sql
来自 sys.syscacheobjects
SQL 不喜欢“%cache%”
ANDsql NOTLIKE ‘%sys.%’;
总结：EXEC 生成了 3 个独立的 ad hoc 执行计划，而使用 SP_EXECUTESQL 只生成了 1 个执行计划并重复使用了 3 次。想象一下，如果一个库中有很多类似的动态SQL，并且它们被频繁执行。如果使用 SP_EXECUTESQL，可以提高性能。
以下是其他网友补充
由于一些特殊原因，我们需要在SQL语句或存储过程中动态创建SQL语句，然后在SQL语句或存储过程中动态执行。
这里，微软提供了两种方法，一种是使用
执行功能
执行方法是
execute(@sql)动态执行一条SQL语句，但是这里的SQL语句无法得到返回结果。还有一个方法
使用存储过程sp_ExecuteSql
使用该存储过程，可以返回动态语句中的参数。
例如
声明@sql nvarchar(800),@dd varchar(20)
set @sql=’set @mm=”测试字符串”’
exec sp_executesql @sql,N'@mm varchar(20) 输出',@dd 输出
选择@dd
执行它会将内部创建的SQL语句中的变量值返回给外部调用者。
主要来自工作中的偶然需要：
创建proc proc_InToServer @收费站点编号varchar(4),@车道编号tinyint,@进入时间varchar(23),@UID char(16),
@车牌 varchar(12),@车型 char(1),@识别车牌号 varchar(12),@识别车型 char(1),@充值金额,@交易状态 char(1),
@有图像位、@离开时间varchar(23)、@速度float、@HasInsert int输出
作为
开始
声明@inTime datetime、@TableName varchar(255)、@leaveTime datetime、@HasTable bit、@Sql nvarchar(4000)
选择@intime=转换(日期时间,@进入时间),@leaveTime=转换(日期时间,@离开时间)
set @TableName='ETC03_01_OBE 原传记录表_'+dbo.formatDatetime(@intime,'YYYYMMDD')
从 sysobjects 中选择 @HasTable=(Case when Count(*)>0 then 1 else 0 end)，其中 id=Object_id(@TableName) 且 ObjectProperty(id,'IsUserTable')=1
如果@HasTable=0
开始
set @Sql='CREATE TABLE [dbo].['+@TableName+'] (
[收费站点编号] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
[车道号] [tinyint] NOT NULL,
[输入时间] [日期时间] NOT NULL,
[UID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL,
[车牌] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[车型] [字符] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[识别车牌号] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[识别车型] [字符] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[充值金额][金额]NULL,
[交易状态] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[有图像] [位] NOT NULL ,
[出发时间] [日期时间] NULL ,
[速度] [浮动] NULL,
约束’+’PK_’+@TableName+’主键（收费站号、车道号、进入时间、UID）
) 在 [小学]’
执行(@Sql)
完
set @sql = 'select @Cnt=count(*) from '+@TableName+ ' where 收费站号码=”'+@收费站号码+”' 车道号码='+cast(@车道号码 as varchar(4) ) +' 且入场时间=”'+@入场时间+”' 且UID=”'+@UID+””
设置@sql = @sql + ‘ if @Cnt=0 ‘
set @sql=@sql+'insert '+@TableName+'values("'+@充电站点编号+"','+cast(@lane number as varchar(4))+',"'+@输入时间+ "',"'+@Uid+"',"'+@车牌+
"',"'+@model+"',"'+ @identify 车牌号+"',"'+@identify 型号+"','+Cast(@charge amount as varchar(8))+'," '+ @交易状态+"','+cast(@有image as varchar(1))+
‘,”’+@出发时间+”’,’+Cast(@speed as varchar(8))+’)’
–执行(@sql)
exec sp_executesql @sql,N'@Cnt int output',@HasInsert 输出
结束
补充信息2、
SQL Server 在循环中执行动态 SQL 语句。
使用 Navicate 工具成功执行查询。
声明@name nvarchar(100)
声明@sql nvarchar(200)
声明@i int
设置@i =10000
同时@i<=99999
开始
设置@name = ‘测试’ +强制转换(@i as varchar(20))
set @sql =N'SELECT * INTO '+ @name +' FROM test'
exec sp_executesql @sql
打印@名字
设置@i=@i + 1
结束
以上是SQL Server执行编辑器编译的动态SQL的正确方法。我希望它能帮助你。
头条
头条
头条
头条
头条
头条
头条
头条
头条
头条
