SQL Server が関連する動的 SQL を実行する正しい方法を説明する

この記事では主に SQL Server が動的 SQL を実行するための正しい方法を紹介します。必要な方は参考にしてください。

SQL Server が動的 SQL を実行する場合、次の 2 つの正しい方法を紹介します。動的 SQL を実行する SQL Server。SQL Server によって実行される動的 SQL についてより深く理解できることを願っています。動的 SQL は、SQL の力を柔軟に発揮できます。他の方法では解決するのが難しい問題を解決するのに便利です。動的 SQL を使用したことがある人なら、その便利さを理解できると思います。実行パフォーマンス (効率) の点で静的 SQL ほど優れていない場合があり、不適切に使用されると、セキュリティ上の危険 (SQL インジェクション攻撃) が潜むことがよくあります。

動的 SQL は、EXECUTE または 2 つの方法で実行できます。 SP_EXECUTESQL。

EXECUTE

Transact-SQL バッチを実行します。文字列内のコマンド文字列、または次のモジュールのいずれかを実行します: システム ストアド プロシージャ、ユーザー定義ストアド プロシージャ、スカラーSQL Server 2005 は、EXECUTE ステートメントを拡張して、サーバーが pass コマンドを送信できるようにします。また、文字列またはコマンドを実行するためのコンテキストを明示的に設定することもできます。 EXECUTE

　　执行 Transact-SQL 批中的命令字符串、字符串或执行下列模块之一:系统存储过程、用户定义存储过程、标量值用户定义函数或扩展存储过程.SQL Server 2005 扩展了 EXECUTE 语句,以使其可用于向链接服务器发送传递命令.此外,还可以显式设置执行字符串或命令的上下文

　　SP_EXECUTESQL

SP_EXECUTESQL

複数回再利用できる、または動的に生成できる Transact-SQL ステートメントまたはバッチを実行します。Transact -SQL ステートメントまたはバッチには、バッチに関して EXECUTE と同じように動作します。 SP_EXECUTESQL stmt パラメータ内の Transact-SQL ステートメントまたはバッチは、SP_EXECUTESQL ステートメントが実行されるまでコンパイルされず、この実行プランは実行プランとして独立しています。 SP_EXECUTESQL という名前のバッチの実行計画。SP_EXECUTESQL バッチは、SP_EXECUTESQL を呼び出すバッチで宣言された変数を参照できません。SP_EXECUTESQL バッチを呼び出すバッチにはローカル カーソルまたは変数が表示されません。データベース コンテキストへの変更は、終了するまで有効です。 SP_EXECUTESQL ステートメント内のパラメータ値のみが変更される場合、Transact-SQL ステートメント自体は変更されず、パラメータ値のみが変更されるため、ストアド プロシージャの代わりに sp_executesql を使用できます。変更すると、SQL Server クエリ オプティマイザーは最初の実行時に生成された実行プランを再利用することがあります

一般に、SP_EXECUTESQL を使用して動的 SQL を実行することを優先することをお勧めします。一方、クエリ オプティマイザーは実行プランを再利用する可能性が高く、実行効率が向上します。もちろん、これは、特定の状況では EXECUTE を完全に放棄する必要があるという意味ではありません。 , たとえば、動的 SQL 文字列は、NVARCHAR 型ではなく VARCHAR 型であり、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 型の文字列定数と変数を実行できます。暗黙的に ntext に変換できます。

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

EXECUTE('SELECT * FROM Groups WHERE GroupName=''' + SUBSTRING(@GroupName, 1,5) + ''''); --'SUBSTRING' の近くに構文エラーがあります。

DECLARE @Sql VARCHAR(200);

DECLARE @GroupName SET@GroupName ='SuperAdmin';

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

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

要約: EXECUTE 括弧文字列変数、文字列定数、またはそれらの組み合わせのみを含めることができます。他の関数やストレージを呼び出すことはできません。使用したい場合は、上記のように変数の組み合わせを使用してください。 @GroupName VARCHAR(50);SET@GroupName ='SuperAdmin';

SET@Sql= 'SELECT * FROM グループ WHEREGroupName=@GroupName'

--PRINT @Sql;EXECUTE(@Sql); --エラー: 必須スカラー変数 "@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 グループ WHEREGroupName=@GroupName'

PRINT @Sql;

EXEC SP_EXECUTESQL @Sql ,N'@グループ名NVARCHAR',@グループ名

クエリに結果がなく、パラメータの長さが宣言されていません。

DECLARE @Sql NVARCHAR(200);

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

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

PRINT @Sql;

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

概要: 動的バッチ処理は、で定義されたローカル変数にアクセスできません。 SP_EXECUTESQL 入力パラメータと出力パラメータを持つことができ、EXECUTE と SP_EXECUTESQL の実行効率を見てみましょう。まず、キャッシュ実行プランをクリアしてから、SuperAdmin を実行するように @GroupName 値を変更します。 、CommonUser、CommonAdmin をそれぞれ 3 回確認してから、他の Use キャッシュ情報

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); ANDsql NOTLIKE '%sys.%';

例に続いて、SP_EXECUTESQLの実行効率を見てみましょう

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;概要: EXEC の生成 3 つの独立したアドホック実行プランがありますが、SP_EXECUTESQL を使用すると、1 つの実行プランしか生成されず、それが 3 回再利用されます。このような動的 SQL がライブラリ内に多数存在し、それらが頻繁に実行される場合を想像してください。 、パフォーマンスを向上させることができます

以下は他のネチズンからの追加です

いくつかの特別な理由により、SQLステートメントまたはストアドプロシージャでSQLステートメントを動的に作成し、それらをSQLステートメントまたはストアドプロシージャ内で動的に実行する必要があります。手順。

ここで、Microsoftは2つの方法を提供しています。1つは

Execute関数を使用する方法です。実行方法は

Execute(@sql)でSQL文を動的に実行しますが、ここでのSQL文は戻り結果を取得できません。別の方法を紹介しましょう

ストアドプロシージャを使用する

sp_ExecuteSql

このストアドプロシージャを使用すると、動的ステートメントでパラメータを返すことができます。

たとえば、

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

が実行されると、内部で作成されたSQL文の変数の値が外部の呼び出し元に返されます。

主に仕事での偶発的な必要性から来ています:

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

補足情報 2.

SQL Server は動的 SQL ステートメントを実行するためにループします。

Navicate ツールを使用してクエリを正常に実行します。

りー

以上がSQL Server が関連する動的 SQL を実行する正しい方法を説明するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。