Expliquer la manière correcte pour SQL Server d'exécuter le SQL dynamique associé

Cet article présente principalement la manière correcte pour SQL Server d'exécuter du SQL dynamique. Les amis qui en ont besoin peuvent s'y référer

Si SQL Server exécute du SQL dynamique, comment doit-il être implémenté ? à la dynamique d'exécution de SQL Server. Deux méthodes correctes de SQL, j'espère que cela pourra vous donner une compréhension plus approfondie de SQL Server exécutant du SQL dynamique

Dynamic SQL : code qui est exécuté dynamiquement. est combiné dynamiquement en fonction des entrées de l'utilisateur ou de conditions externes. .Dynamic SQL peut exercer de manière flexible les fonctions puissantes de SQL et résoudre facilement certains problèmes difficiles à résoudre par d'autres méthodes. Je pense que quiconque a utilisé le SQL dynamique peut en apprécier la commodité. apporte. Cependant, le SQL dynamique souffre parfois des performances d'exécution (efficacité) ) Ce qui précède n'est pas aussi bon que le SQL statique, et s'il est mal utilisé, il existe souvent des dangers cachés en matière de sécurité (attaques par injection SQL

Dynamic SQL peut être exécuté de deux manières : EXECUTE ou SP_EXECUTESQL.

　

EXECUTE

Exécutez la chaîne de commande, la chaîne dans le lot Transact-SQL ou exécutez l'un des modules suivants : procédure stockée système, procédure stockée définie par l'utilisateur, fonction définie par l'utilisateur à valeur scalaire ou procédure stockée étendue. SQL Server 2005 a étendu l'instruction EXECUTE afin qu'elle puisse être utilisée pour envoyer des commandes pass à un serveur lié. peut également définir explicitement le contexte d'exécution d'une chaîne ou d'une commande

　

SP_EXECUTESQL

Exécuter une instruction ou un lot Transact-SQL qui peut être réutilisé plusieurs fois ou généré dynamiquement. ou le lot peut contenir des paramètres incorporés. En termes de lots, de portées de nom et de contextes de base de données, SP_EXECUTESQL est identique à EXECUTE. Le comportement est le même. L'instruction ou le lot Transact-SQL dans le paramètre stmt SP_EXECUTESQL n'est pas compilé tant que l'instruction SP_EXECUTESQL n'est pas compilée. est exécuté. Par la suite, le contenu du stmt est compilé et exécuté comme un plan d'exécution. Ce plan d'exécution est indépendant du plan d'exécution nommé SP_EXECUTESQL Le plan d'exécution du batch SP_EXECUTESQL Le batch ne peut pas référencer les variables déclarées dans le batch qui appelle SP_EXECUTESQL. . Les curseurs ou variables locaux dans le lot SP_EXECUTESQL ne sont pas visibles par le lot qui appelle SP_EXECUTESQL. Les modifications du contexte de la base de données ne sont effectuées que dans SP_EXECUTESQL Valable jusqu'à la fin de l'instruction

Si seules les valeurs des paramètres. dans l'instruction sont modifiés, sp_executesql peut être utilisé pour exécuter l'instruction Transact-SQL plusieurs fois au lieu de la procédure stockée. Étant donné que l'instruction Transact-SQL elle-même reste inchangée, seules les valeurs des paramètres changent, donc la requête SQL Server. L'optimiseur peut réutiliser le plan d'exécution généré lors de la première exécution.

De manière générale, nous recommandons et donnons la priorité à l'utilisation de SP_EXECUTESQL pour exécuter du SQL dynamique. D'une part, il est plus flexible et peut avoir des paramètres de sortie d'entrée. D'un autre côté, l'optimiseur de requêtes est plus susceptible de réutiliser les plans d'exécution et d'améliorer l'efficacité de l'exécution. De plus, l'utilisation de SP_EXECUTESQL peut bien sûr améliorer la sécurité, cela ne signifie pas que EXECUTE doit être complètement abandonné dans certaines situations. efficace que SP_EXECUTESQL. Par exemple, les chaînes SQL dynamiques sont de type VARCHAR, et non de type NVARCHAR. SP_EXECUTESQL ne peut exécuter que des chaînes Unicode, des constantes ou des variables qui peuvent être implicitement converties en ntext, tandis que EXECUTE peut exécuter les deux types de chaînes. .

Comparons maintenant quelques détails de EXECUTE et SP_EXECUTESQL.

EXECUTE(N'SELECT * FROM Groups') --Exécution réussie

EXECUTE('SELECT * FROM Groups') --Exécution réussie

SP_EXECUTESQL N'SELECT * FROM Groups'; --Exécution réussie

SP_EXECUTESQL 'SELECT * FROM Groups' -- Erreur d'exécution

Résumé : EXECUTE peut exécuter des constantes et des variables de chaîne de type non Unicode ou Unicode. SP_EXECUTESQL ne peut exécuter que des constantes et des variables Unicode ou de chaîne qui peuvent être implicitement converties en ntext.

DECLARE @GroupName VARCHAR(. 50);SET@GroupName ='SuperAdmin';

EXECUTE('SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING (@GroupName, 1,5) + ''' '); est une erreur de syntaxe proche de 'SUBSTRING'.

DECLARE @Sql VARCHAR(200);

DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';

　SET@Sql='SELECT * FROM Groupes WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''

　--PRINT @Sql;EXECUTE(@ Sql);

Résumé : EXECUTE Les crochets ne peuvent contenir que des variables de chaîne, des constantes de chaîne ou leurs combinaisons, et ne peuvent pas appeler d'autres fonctions, procédures stockées, etc. Si vous souhaitez les utiliser, utilisez des combinaisons de variables, comme indiqué ci-dessus.

DECLARE @Sql VARCHAR(200);

DECLARE @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';

SET@Sql= 'SELECT * FROM Groupes WHEREGroupName=@GroupName'

　--PRINT @Sql;EXECUTE(@Sql); --Erreur : doit déclarer la variable scalaire "@GroupName".SET@Sql='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME (@GroupName, '''')

EXECUTE (@Sql); --Correct :

DECLARE @Sql NVARCHAR(200);

DECLARE @GroupName NVARCHAR (50);SET@GroupName ='SuperAdmin';

SET@Sql='SELECT * FROM Groupes WHEREGroupName=@GroupName'

PRINT @Sql;

EXEC SP_EXECUTESQL @Sql,N'@GroupNameNVARCHAR',@GroupName

Il n'y a aucun résultat dans la requête et la longueur du paramètre n'est pas déclarée

DECLARE @Sql NVARCHAR(200);

DECLARE @GroupName NVARCHAR(50); ='SuperAdmin';

　SET@Sql ='SELECT * FROM Groupes WHERE GroupName=@GroupName'

　PRINT @Sql;

　EXEC SP_EXECUTESQL @Sql, N'@ GroupName NVARCHAR(50)' ,@GroupName

Résumé : le traitement par lots dynamique ne peut pas accéder aux variables locales définies dans le lot. SP_EXECUTESQL peut avoir des paramètres d'entrée et de sortie, ce qui est plus flexible que EXECUTE.

. Jetons un coup d'œil à EXECUTE, SP_EXECUTESQL Pour plus d'efficacité d'exécution, effacez d'abord le plan d'exécution du cache, puis modifiez la valeur @GroupName et exécutez SuperAdmin, CommonUser et CommonAdmin trois fois respectivement. Examinez ensuite les informations de cache utilisées

. DBCC FREEPROCCACHE ;

DECLARE @Sql VARCHAR(200);

DECLARE @GroupName VARCHAR(50); >

SET@Sql ='SELECT * FROM Groups WHERE GroupName=' + QUOTENAME(@GroupName, '''')

EXECUTE (@Sql); SELECTcacheobjtype, objtype, usecounts, sql

FROM sys.syscacheobjects

　WHERE sql NOTLIKE '%cache%'

　ANDsql NOTLIKE '%sys.%';

En suivant le même exemple, prenons un regardez l'efficacité d'exécution de SP_EXECUTESQL

DBCC FREEPROCCACHE;

DECLARE @Sql NVARCHAR(200);

DECLARE @GroupName NVARCHAR(50);SET@GroupName ='SuperAdmin '; --'CommonUser', 'CommonAdmin'

SET@Sql ='SELECT * FROM Groupes WHERE GroupName=@GroupName'

EXECUTESP_EXECUTESQL @Sql, N'@GroupName NVARCHAR(50) ', @GroupName;

SELECTcacheobjtype , objtype, usecounts, sql

FROM sys.syscacheobjects

WHERE sql NOTLIKE '%cache%'

ANDsql NOTLIKE '%sys.%';

Résumé : EXEC a généré trois plans d'exécution ad hoc indépendants, tandis que SP_EXECUTESQL n'a généré qu'un seul plan d'exécution et l'a réutilisé trois fois. Imaginez s'il existe de nombreux SQL dynamiques similaires dans une bibliothèque et qu'ils le sont. sont exécutés fréquemment. Si vous utilisez SP_EXECUTESQL, vous pouvez améliorer les performances

Ce qui suit sont des ajouts d'autres internautes

Pour certaines raisons particulières, nous devons créer dynamiquement des instructions SQL dans. Instructions SQL ou procédures stockées, puis exécutez dynamiquement des instructions SQL ou des procédures stockées.

Ici, Microsoft propose deux méthodes, l'une consiste à utiliser la fonction

Execute

La méthode d'exécution est

Execute(@sql) Exécutez dynamiquement une instruction SQL, mais l'instruction SQL ici ne peut pas obtenir le résultat de retour. Voici une autre méthode

utilisant la procédure stockée

sp_ExecuteSql

utilisant la procédure de stockage, vous peut renvoyer les paramètres dans l'instruction dynamique.

Par exemple, si

declare @sql nvarchar(800),@dd varchar(20)
set @sql='set @mm=''测试字符串'''
exec sp_executesql @sql,N'@mm varchar(20) output',@dd output
select @dd

est exécuté, la valeur d'une variable dans l'instruction SQL créée en interne sera renvoyée à l'appelant externe.

Provient principalement d'un besoin accidentel au travail :

create proc proc_InToServer @收费站点编号 varchar(4),@车道号 tinyint,@进入时间 varchar(23),@UID char(16),
@车牌 varchar(12),@车型 char(1),@识别车牌号 varchar(12),@识别车型 char(1),@收费金额 money,@交易状态 char(1),
@有图像 bit,@离开时间 varchar(23),@速度 float,@HasInsert int output
as
begin
  declare @inTime datetime,@TableName varchar(255),@leaveTime datetime,@HasTable bit,@Sql nvarchar(4000)
 select @intime=Convert(datetime,@进入时间),@leaveTime=Convert(datetime,@离开时间)
 set @TableName='ETC03_01_OBE原始过车记录表_'+dbo.formatDatetime(@intime,'YYYYMMDD')

 select @HasTable=(Case when Count(*)>0 then 1 else 0 end) from sysobjects where id=Object_id(@TableName) and ObjectProperty(id,'IsUserTable')=1
 if @HasTable=0
 begin
  set @Sql='CREATE TABLE [dbo].['+@TableName+'] (
 [收费站点编号] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [车道号] [tinyint] NOT NULL,
 [进入时间] [datetime] NOT NULL,
 [UID] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [车牌] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [车型] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [识别车牌号] [varchar] (12) COLLATE Chinese_PRC_CI_AS NULL ,
 [识别车型] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [收费金额] [money] NULL ,
 [交易状态] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [有图像] [bit] NOT NULL ,
 [离开时间] [datetime] NULL ,
 [速度] [float] NULL,
    Constraint'+' PK_'+@TableName+' primary key(收费站点编号,车道号,进入时间,UID)
    ) ON [PRIMARY]'
   Execute(@Sql)
  end 
  set @sql = 'select @Cnt=count(*) from '+@TableName+ ' where 收费站点编号='''+@收费站点编号+''' and 车道号='+cast(@车道号 as varchar(4))+' and 进入时间='''+@进入时间+''' and UID='''+@UID+''''
  set @sql = @sql + ' if @Cnt=0 '
  
  set @sql=@sql+'insert '+@TableName+' values('''+@收费站点编号+''','+cast(@车道号 as varchar(4))+','''+@进入时间+''','''+@Uid+''','''+@车牌+  
  ''','''+@车型+''','''+  @识别车牌号+''','''+@识别车型+''','+Cast(@收费金额 as varchar(8))+','''+@交易状态+''','+cast(@有图像 as varchar(1))+
  ','''+@离开时间+''','+Cast(@速度 as varchar(8))+')'
  --Execute(@sql) 
  exec sp_executesql @sql,N'@Cnt int output',@HasInsert output
end

Informations supplémentaires 2.

Boucles SQL Server pour exécuter du SQL dynamique Déclaration.

Utilisation de l'outil Navicate pour exécuter la requête avec succès.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!