Erklären Sie, wie SQL Server zugehöriges dynamisches SQL korrekt ausführt

In diesem Artikel wird hauptsächlich die richtige Art und Weise für die Ausführung von dynamischem SQL durch SQL Server vorgestellt.

Wenn SQL Server dynamisches SQL ausführt, wird es Ihnen im Folgenden vorgestellt Ich hoffe, dass es Ihnen ein tieferes Verständnis der SQL Server-Ausführungsdynamik vermittelt.

Dynamisches SQL: Code, der dynamisch ausgeführt wird wird basierend auf Benutzereingaben oder externen Bedingungen dynamisch kombiniert. Dynamisches SQL kann die leistungsstarken Funktionen von SQL flexibel nutzen und einige Probleme bequem lösen, die mit anderen Methoden schwer zu lösen sind. Ich glaube, dass jeder, der dynamisches SQL verwendet hat, die Bequemlichkeit zu schätzen weiß Allerdings leidet dynamisches SQL manchmal unter der Ausführungsleistung (Effizienz). Das oben Genannte ist nicht so gut wie statisches SQL, und bei unsachgemäßer Verwendung gibt es oft versteckte Gefahren in Bezug auf die Sicherheit (SQL-Injection-Angriffe). 🎜> Dynamisches SQL kann auf zwei Arten ausgeführt werden: EXECUTE oder SP_EXECUTESQL.

　

Führen Sie die Befehlszeichenfolge, die Zeichenfolge im Transact-SQL-Batch aus oder führen Sie eine davon aus Folgende Module: gespeicherte Systemprozedur, benutzerdefinierte gespeicherte Prozedur, benutzerdefinierte Skalarwertfunktion oder erweiterte gespeicherte Prozedur. SQL Server 2005 hat die EXECUTE-Anweisung so erweitert, dass sie zum Senden von Pass-Befehlen an einen Verbindungsserver verwendet werden kann kann auch explizit den Kontext für die Ausführung einer Zeichenfolge oder eines Befehls festlegen EXECUTE

　

Führen Sie eine Transact-SQL-Anweisung oder einen Batch aus, der mehrmals wiederverwendet oder dynamisch generiert werden kann oder Batch kann eingebettete Parameter enthalten. In Bezug auf Batches, Namensbereiche und Datenbankkontexte ist SP_EXECUTESQL dasselbe wie EXECUTE. Das Verhalten ist das gleiche. Die Transact-SQL-Anweisung oder der Batch im SP_EXECUTESQL-STMT-Parameter wird erst mit der SP_EXECUTESQL-Anweisung kompiliert Anschließend wird der Inhalt des stmt kompiliert und als Ausführungsplan ausgeführt, der SP_EXECUTESQL heißt. Der Ausführungsplan des Batches kann nicht auf Variablen verweisen, die im Batch deklariert sind . Lokale Cursor oder Variablen im SP_EXECUTESQL-Batch sind für den Batch, der SP_EXECUTESQL aufruft, nicht sichtbar. Änderungen am Datenbankkontext werden nur bis zum Ende der Anweisung vorgenommen in der Anweisung geändert werden, kann sp_executesql verwendet werden, um die Transact-SQL-Anweisung mehrmals auszuführen, anstatt die gespeicherte Prozedur. Da die Transact-SQL-Anweisung selbst unverändert bleibt, ändern sich nur die Parameterwerte, also die SQL Server-Abfrage Der Optimierer kann den bei der ersten Ausführung generierten Ausführungsplan wiederverwenden. SP_EXECUTESQL

Im Allgemeinen empfehlen wir die Verwendung von SP_EXECUTESQL, um dynamisches SQL auszuführen. Einerseits ist es flexibler und kann über Eingabe- und Ausgabeparameter verfügen. Andererseits ist es wahrscheinlicher, dass der Abfrageoptimierer Ausführungspläne wiederverwendet und die Ausführungseffizienz verbessert. Darüber hinaus bedeutet dies natürlich nicht, dass EXECUTE vollständig aufgegeben werden sollte Effizienter als SP_EXECUTESQL. Dynamische SQL-Zeichenfolgen sind beispielsweise vom Typ VARCHAR, nicht vom Typ NVARCHAR. SP_EXECUTESQL kann nur Unicode-Zeichenfolgen oder -Konstanten oder -Variablen ausführen, die implizit in ntext konvertiert werden können .

Vergleichen wir einige Details von EXECUTE und SP_EXECUTESQL.

EXECUTE(N'SELECT * FROM Groups') --Ausführung erfolgreich

　EXECUTE ('SELECT * FROM Groups') --Ausführung erfolgreich

SP_EXECUTESQL N'SELECT * FROM Groups' --Ausführung erfolgreich

SP_EXECUTESQL 'SELECT * FROM Groups' -- Ausführungsfehler

Zusammenfassung: EXECUTE kann Nicht-Unicode- oder Unicode-String-Konstanten und -Variablen ausführen. SP_EXECUTESQL kann nur Unicode- oder String-Konstanten und -Variablen ausführen, die implizit in ntext konvertiert werden können.

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

EXECUTE('SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''' '); ein Syntaxfehler in der Nähe von „SUBSTRING“.

DECLARE @Sql VARCHAR(200);

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

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

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

Zusammenfassung: EXECUTE Die Klammern können nur Zeichenfolgenvariablen, Zeichenfolgenkonstanten oder deren Kombinationen enthalten und können keine anderen Funktionen, gespeicherten Prozeduren usw. aufrufen. Wenn Sie sie verwenden möchten, verwenden Sie Variablenkombinationen, wie oben gezeigt.

DECLARE @Sql VARCHAR(200);

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

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

　--PRINT @Sql;EXECUTE(@Sql); --Error: Skalare Variable „@GroupName“ muss deklariert werden.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 Groups WHEREGroupName=@GroupName'

PRINT @Sql;

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

Es gibt kein Ergebnis in der Abfrage und die Parameterlänge ist nicht deklariert

DECLARE @Sql NVARCHAR(200);

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

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

　PRINT @Sql;

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

Zusammenfassung: Die dynamische Stapelverarbeitung kann nicht auf im Stapel definierte lokale Variablen zugreifen. SP_EXECUTESQL kann Eingabe- und Ausgabeparameter haben, was flexibler ist als EXECUTE.

Werfen wir einen Blick auf EXECUTE, SP_EXECUTESQL. Um die Ausführungseffizienz zu gewährleisten, löschen Sie zunächst den Cache-Ausführungsplan, ändern Sie dann den @GroupName-Wert und führen Sie SuperAdmin, CommonUser und CommonAdmin jeweils dreimal aus. Sehen Sie sich dann die verwendeten Cache-Informationen an

DBCC FREEPROCCACHE;

DECLARE @Sql VARCHAR(200);

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

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.%';

Folgen wir dem gleichen Beispiel und nehmen wir ein Schauen Sie sich die Ausführungseffizienz von SP_EXECUTESQL an

DBCC FREEPROCCACHE;

DECLARE @Sql NVARCHAR(200);

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

SET@Sql ='SELECT * FROM Groups 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.%';

Zusammenfassung: EXEC hat drei unabhängige Ad-hoc-Ausführungspläne generiert, während SP_EXECUTESQL nur einen Ausführungsplan generiert und ihn dreimal wiederverwendet hat. Stellen Sie sich vor, es gäbe viele ähnliche dynamische SQLs in einer Bibliothek werden häufig ausgeführt. Wenn Sie SP_EXECUTESQL verwenden, kann die Leistung verbessert werden.

Das Folgende sind Ergänzungen von anderen Internetnutzern.

Aus bestimmten Gründen müssen wir SQL-Anweisungen dynamisch erstellen SQL-Anweisungen oder gespeicherte Prozeduren ausführen und dann SQL-Anweisungen oder gespeicherte Prozeduren dynamisch ausführen.

Hier stellt Microsoft zwei Methoden bereit, eine besteht darin, die

Execute-Funktion zu verwenden

Die Ausführungsmethode ist
Execute(@sql) Führen Sie eine SQL-Anweisung dynamisch aus, aber die SQL-Anweisung hier kann das Rückgabeergebnis nicht abrufen. Hier ist eine andere Methode

, die die gespeicherte Prozedur sp_ExecuteSql

verwendet kann die Parameter in der dynamischen Anweisung zurückgeben.

Wenn beispielsweise

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

ausgeführt wird, wird der Wert einer Variablen in der intern erstellten SQL-Anweisung an den externen Aufrufer zurückgegeben.

Entsteht hauptsächlich aus einem zufälligen Bedarf am Arbeitsplatz:

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

Ergänzende Informationen 2.

SQL Server-Schleifen zur Ausführung von dynamischem SQL Aussage.

Mit dem Navicate-Tool die Abfrage erfolgreich ausführen.

declare @name nvarchar(100)

declare @sql nvarchar(200)

declare @i int
set @i =10000

while @i<=99999
begin
	set @name = &#39;test&#39; + cast(@i as varchar(20))
	set @sql =N&#39;SELECT * INTO &#39;+ @name +&#39; FROM test&#39;
	exec sp_executesql @sql
	print @name

 set @i=@i + 1
end

Das obige ist der detaillierte Inhalt vonErklären Sie, wie SQL Server zugehöriges dynamisches SQL korrekt ausführt. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!