T-SQLで動的クエリにパラメーターを渡す

動的なクエリを作成する必要性に繰り返し遭遇しましたが、以下で説明する落とし穴がいくつかあります。 動的クエリの例：

declare @sql varchar(100) = 'select 1+1' execute( @sql)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

1. Executeを介して行を実行すると、現在の変数は表示されませんが、すべての一時テーブルが表示されるコードブロックが個別に作成されます。



2. NULL値を持つ変数の受け渡しに注意してください。 NULLとのマージはNULLになります。したがって、クエリを実行する代わりに、空の文字列を取得できます。

 declare @i int declare @sql varchar(100) = 'select ' + cstr(@i) execute( @sql ) -- 
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

3.日付と時刻を転送します。 日付はYYYYMMDD形式で送信するのが最適です。 経時的にパラメーターを渡す場合、精度の低下に注意する必要があります。 精度を維持するには、一時テーブルを介して値を渡すのが最善です。



4.浮動小数点でパラメーターを渡すことには、構築されたクエリ内で時間を渡すことと同じ問題があります。



5.文字列値-潜在的に危険なコード。 まず、文字列内ですべての単一引用符を複製する必要があります。 文字列自体は一重引用符で囲まれます。



エラーコードの例：

 Declare @str varchar(100) = 'Number ''1'' ' Declare @sql varchar(1000) = 'select String = '+ IsNull( '''' + @str + '''', 'null' ) Execute( @sql ) --    
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

正しいコードは次のとおりです。

 Declare @str varchar(100) = 'Number ''1'' ' Declare @sql varchar(1000) = 'select String = '+ IsNull( '''' + replace( @str, '''', '''''') + '''', 'null' ) Execute( @sql )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

6.セクションINのリストの置換。 主な危険は空のリストです。 この場合、セクションは「フィールドIN（）」のようになり、コンパイル中にエラーが発生します。 闘争の方法として：常にリストの先頭にNULLを含めるか、空の文字列をNULLに置き換えます。 NULLは任意のデータ型と比較できます。 NULLとの比較では常に負の結果が得られますが、リストは空ではないことが保証されています。

 Declare @list varchar(100) = '' iif @list = '' set @list = 'null' Declare @sql varchar(1000) = 'select number from documents where id in ('+@list+') ' Execute( @sql )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

一時テーブルを介して複雑なパラメータを安全に渡す例を次に示します。

 if OBJECT_ID('tempdb..#params') is not null drop table #params create table #params ( v1 int, v2 datetime, v3 varchar(100) ) insert #params values ( 1, getdate(), ' ''1''') declare @sql varchar(1000) = ' declare @v1 int, @v2 datetime, @v3 varchar(100) select @v1 = v1 , @v2 = v2, @v3 = v3 from #params select @v1, @v2, @v3 ' execute(@sql) drop table #params
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

さて、ちょっとした前菜の場合：



最初に変数を介して転送されたパラメーターを宣言し、これらの変数を初期化し、計算中にこれらの変数を使用することをお勧めします。 この場合、クエリテキストの読みやすさが向上し、デバッグが容易になります。



変数を使用しない場合、次の方法を使用できます。

 set @sql = 'select <VAR1> + <VAR2>' set @sql = replace(@sql, '<VAR1>', '1') set @sql = replace(@sql, '<VAR2>', '2') execute( @sql )
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

上記の機能に加えて、パラメーターを渡す方法がいくつかあります。

1. sp_executesqlを使用します（コメントでプロンプトが表示されます）

2.リクエストを一時的なストアドプロシージャにラップして実行します。 開始回数が多い場合、この方法はさらに効果的です。

 declare @sql varchar(200) = ' create procedure #test ( @p1 int, @p2 int) as select @p1 + @p2' execute( @sql ) exec #test 1, 2 exec #test 3,4 drop procedure #test