sq_executesql の使い方 - 月のあかり

SQLの文字列を組み合わせて作成をし、動的に発行するプログラムをVBやC#などアプリケーションで作成したことがあると思う。
TSQLにおいてこのような文字列で動的に変化するSQLを実行するためのものとしてsp_executesql という組み込みストアドが存在する。

◇sp_executesql の構文(SQL Server2005 Helpより引用)

sp_executesql [ @stmt = ] stmt
[
{, [@params=] N'@parameter_name data_type [ [ OUT [ PUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]

1. 単純なSELECT の例
※M_郵便番号テーブルの郵便番号の数を取得する例
@InnerSQL にSELECT文を埋め込み、実行する

DECLARE @InnerSQL NVARCHAR(2000)
SET @InnerSQL = 'SELECT COUNT(PostalCode7) FROM dbo.M_郵便番号 '
EXEC sp_executesql @InnerSQL

2. 条件句(WHERE句)にパラメータ変数を用いる例
※M_郵便番号テーブルから大阪府の郵便番号数を取得する

DECLARE @InnerSQL NVARCHAR(2000)
DECLARE @ParmSQL NVARCHAR(2000)
SET @InnerSQL = 'SELECT COUNT(PostalCode7) FROM dbo.M_郵便番号 '
SET @InnerSQL = @InnerSQL + 'WHERE 都道府県名 = @Pref '
SET @ParmSQL = '@Pref NVARCHAR(20)'
EXEC sp_executesql @InnerSQL
,@ParmSQL
,@Pref = '大阪府'

3. 条件句を設定し、該当するデータ数を取得する例
※M_郵便番号テーブルから大阪府の郵便番号数を取得し、動的SQL内部で内部変数に取得→外部変数へ代入

/*■動的SQL内部で変数に代入して取得する例*/
DECLARE @InnerSQL NVARCHAR(2000)
DECLARE @ParmSQL NVARCHAR(2000)
DECLARE @CNT INT

SET @InnerSQL = 'SELECT @P_OUT = COUNT(PostalCode7) FROM dbo.M_郵便番号 '
SET @InnerSQL = @InnerSQL + 'WHERE 都道府県名 = @Pref; '
SET @ParmSQL = '@Pref NVARCHAR(20) , @P_OUT INT OUTPUT'
EXEC sp_executesql
@InnerSQL /*実行するSQL*/
, @ParmSQL /*内部で使う変数の定義(外部へ渡す場合OUTPUTをつける*/
, @Pref = '大阪府' /*内部変数への代入式*/
, @P_OUT=@CNT OUTPUT /*内部変数から外部変数への代入(向きが逆な点に注意）*/
SELECT @CNT 取得@P_OUT

◇sp_executesqlのメリット
　上記のようにTSQL で複雑なパラメータの設定や取得値を変数に代入して取得する動的SQLが実現可能ということが理解できたと思う。ではこの組込みストアドを使うメリットはどこにあるのか？
動的にSQLを作成しようとする目的は、
　・パラメータを繰り返し変更しながらデータを取得したい場合
　・テーブル名やDB名を動的に変更して取得したい場合
などが考えられる。
このうち、パラメータを繰り返し変更しながらデータを取得するようなケースでは同じSQL構文でありながらパラメータを代入する使い方は、メインのSQL ステートメント部（上記例では@InnerSQLの部位）をクエリオプティマイザが最初の実行プランを再利用して実行されるため、実行ロスを大幅に抑えることができる。

その違いを見てみる。
△実行する処理△
M_郵便番号テーブルを都道府県、市町村区ごとに郵便番号を取得して一時テーブルへ格納する。

1. 通常のSQLで実行
このSQLを実行したとき、私のPCで１分１７秒かかった。

2. sp_executesql を使って同じ処理を実行
　sp_executesqlを使うと28秒で処理が完了。単純に考えて倍以上早くなったことになる。

◇まとめ
今回はsp_executesql の機能や使い方のひとつとして例をあげてみた。アプリケーション側でSQLを動的に作成するという手法をとることは多いと思うが、TSQL 側で同様な処理を作ることができるという認識が広がれば、無用に難しい動的SQLをアプリケーションで作成することなく、DB階層での処理で切り分けられることもあると考える。

昨今ではSQLインジェクション攻撃が多発し、機密情報が漏洩する事故もよく耳にする。アプリケーション側で動的SQL作成する場合、SQLインジェクション対策もすべて個別アプリケーションで実施しなければならなくなり、ともすると実装なしでシステムが運用されている例もよく見かける。その意味でSQLServer 側処理するほうが安全で、監査しやすいというメリットがある。
このような観点から、sp_executesqlの使い方がシステム開発の場で役立てば幸いである。

以上