sq_executesql の使い方

SQL Server


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の使い方がシステム開発の場で役立てば幸いである。

以上


ログテーブルへログを登録する

SQL Server

複雑なストアドでエラーが発生した場合、どのような状態でエラーになったのかを検証することが難しいというケースはよくある。特にビジネスロジックを組み合わせた処理で問題が発生すると業務に支障を最小限とするためストアドの実行をロールバックする仕組みを採用していることが多いだろう。

ロールバックは「データを健全に保つ」意味で非常に大事だといことはよく理解されているが、実際には何らかの問題が発生してロールバック処理が実行された原因を追究しなければならない。適切にログを残していたつもりがロールバック処理でログも残っていなかったという経験があるのではないだろうか？

今回はロールバック処理でもログを残せる方法を一例にしながら

■SQL Server 2005 における
　#table(一時テーブル) と@table(変数テーブル) の違いを書いてみたい

◇#table(一時テーブル)の作成

CREATE TABLE #AAA
([Ref_Date] DATETIME
,[Ref_Num] INT
,[Ref_Nomen] NVARCHAR(50)
)

◇@table(変数テーブル)の作成

DECLARE @LOG_TABLE AS TABLE
([Ref_Date] DATETIME
,[Ref_Num] INT
,[Ref_Nomen] NVARCHAR(50)
)

◇一時テーブルはロールバックしてしまう
前述のように、一時テーブルのデータはトランザクションロールバックが実行されるとロールバックする。

上の結果表示で'ROLLBACK 後' の結果表示が出てないことがわかる。

◇変数テーブルはロールバックしない

一時テーブルの結果と違い'ROLLBACK 後' の結果表示が出ている。


◇ログテーブルにデータを格納し、一時テーブルで保存した場合
実行したコード


/* #Table(シャープテーブル)はロールバックする*/
DECLARE @Ref_Status NVARCHAR(255)
DECLARE @Object_Nomen NVARCHAR(50)
DECLARE @Seq_Num INT

SET @Object_Nomen = 'TEST_Object'
SET @Ref_Status = 'TEST Status'
SET @Seq_Num = 0

/*(1) 一時テーブルを作成*/
CREATE TABLE #LOG_TABLE
([Log_Date] DATETIME
,[Local_Num] INT
,[DB_Nomen] NVARCHAR(50)
,[Ref_Status] NVARCHAR(255)
,[Object_Nomen] NVARCHAR(50)
,[Record_Date] DATETIME
,[Recorder_Num] NVARCHAR(20)
)

BEGIN TRY

BEGIN TRAN

/*▼(2) 処理を実行*/
WHILE (SELECT @Seq_Num) < 5
BEGIN
SELECT TOP 1 @Seq_Num Cnt_Num, PostalCode7, 都道府県名, 市区町村名, 町域名 FROM M_郵便番号
SET @Seq_Num = @Seq_Num + 1

/*処理内容を一時テーブルに格納*/
INSERT INTO #LOG_TABLE
SELECT GETDATE(),@Seq_Num,'TEST_DB',@Ref_Status,'TEST_OBJECT',GETDATE(),'TEST_RECORDER'
/*ループの3回目でERROR を強制発行*/
IF @Seq_Num = 3 BEGIN
RAISERROR (@Ref_Status, 18, 1)
END

CONTINUE
END
/*▲*/
COMMIT TRAN -- TRANSACTION COMMIT

END TRY
BEGIN CATCH

/*ロールバック処理*/
ROLLBACK TRAN

/*エラーログを格納*/
INSERT INTO dbo.log_status
SELECT * FROM #LOG_TABLE

END CATCH

DROP TABLE #LOG_TABLE
SELECT * FROM dbo.log_status

DELETE FROM dbo.log_status

上記の例では郵便番号テーブルを５回一行ずつ出力します。繰り返す間にログを一時テーブルに格納し、３回目でRAISERROR()関数でエラーを強制的に発生さる。
TRY CATCH の中でロールバックしたあと実テーブルへログデータを登録する。

結果表示

最後の実テーブルをSELECT した結果表示にログデータがないことがわかる。

◇ログテーブルにデータを格納し、変数テーブルで保存した場合
実行したコード


/* @Table(変数テーブル)はロールバックしない*/
DECLARE @Ref_Status NVARCHAR(255)
DECLARE @Object_Nomen NVARCHAR(50)
DECLARE @Seq_Num INT

SET @Object_Nomen = 'TEST_Object'
SET @Ref_Status = 'TEST Status'
SET @Seq_Num = 0

/*(1) 一時テーブルを作成*/
DECLARE @LOG_TABLE AS TABLE
([Log_Date] DATETIME
,[Local_Num] INT
,[DB_Nomen] NVARCHAR(50)
,[Ref_Status] NVARCHAR(255)
,[Object_Nomen] NVARCHAR(50)
,[Record_Date] DATETIME
,[Recorder_Num] NVARCHAR(20)
)

BEGIN TRY

BEGIN TRAN

/*▼(2) 処理を実行*/
WHILE (SELECT @Seq_Num) < 5
BEGIN
SELECT TOP 1 @Seq_Num Cnt_Num, PostalCode7, 都道府県名, 市区町村名, 町域名 FROM M_郵便番号
SET @Seq_Num = @Seq_Num + 1

/*処理内容を一時テーブルに格納*/
INSERT INTO @LOG_TABLE
SELECT GETDATE(),@Seq_Num,'TEST_DB',@Ref_Status,'TEST_OBJECT',GETDATE(),'TEST_RECORDER'
/*ループの3回目でERROR を強制発行*/
IF @Seq_Num = 3 BEGIN
RAISERROR (@Ref_Status, 18, 1)
END

CONTINUE
END
/*▲*/
COMMIT TRAN -- TRANSACTION COMMIT

END TRY
BEGIN CATCH

/*ロールバック処理*/
ROLLBACK TRAN
/*エラーログを格納*/

INSERT INTO dbo.log_status
SELECT * FROM @LOG_TABLE

END CATCH

SELECT * FROM dbo.log_status

DELETE FROM dbo.log_status

一時テーブルで実施したコードを変数テーブルで実現してみた。
結果表示

最後の実テーブルをSELECTした結果にデータが格納されていることがわかる。

◇ロールバックの影響をよく考える
今回紹介したのは開発作業において処理内容をログ出力するような機能を提供する場合に役に立つという一例と、ストアドの実行にはトランザクション処理（コミット/ロールバック）は欠かせないものであり、使われる変数テーブルや一時テーブルがどのように作用するのかということを理解することに役立つと考えている。

使い方を誤ると、業務そのものに支障をきたすような場面もあり、システムの健全性・確実なトレース機能の実装・もd内が発生したときにトレース情報を確実かつ画一的な検証を可能にする機能実装を考慮していく必要がある。
その意味で、ストアドの書き方、ロールバックのかけ方、ログの出し方などプロジェクトで明確な方針を決めて作りこむことを推奨したい。

TSQL のストアドでTRY CATCH の使い方を紹介してきた。

SQL Server

ではカスタムエラーをどう実現するかということになるがTSQLにRAISERROR()という関数が用意されている。
今回はこのRAISERROR()の使い方を簡単に紹介しよう。

◇基本的な構文

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

◇メッセージの作成

1. メッセージ文字の指定
まず、最初の引数　{ msg_id | msg_str | @local_variable } に注目してみると３つの使い分けができるという意味である。これを簡単に書くと以下の通り

・msg_id ---- メッセージIDを指定する(あらかじめメッセージ登録が必要)
・msg_str ---- メッセージ文字を指定(メッセージ中に動的な変数も使える)
・@local_variable ---- msg_strを変数に格納して提供することができる

３つの使い分けと述べたが実際はあらかじめDBにメッセージを登録して使う方法とTSQL内で生成してメッセージを出力する場合の違いである。




msg_id を使うにはあらかじめ sp_addmessage を使って登録しますが、実際の開発現場ではほとんど使うことがないので今回は省く。

1.-1msg_str の使い方
msg_str はC 標準ライブラリに含まれる printf 関数に似た形式のユーザー定義メッセージである。エラー メッセージの長さは最大 2,047 文字まで。

文字をメッセージとして戻す単純な方法

RAISERROR ('TEST ERROR 発生', -- メッセージID.
10, -- 重大度,
1) -- State
GO

メッセージ内に数値変数を入れて生成する方法

メッセージ内に文字を入れて生成する場合と文字の位置、長さを指定する方法

置き換え文字の種類（Help から)


1.-2　@local_variableを使った方法
@local_variable を使うのはメッセージを動的に変更したい場合などに有効である。
上記で述べてきたmsg_str を変数に入れて使うことにより、適切な動的メッセージを作ることができる。

画像だと大きくなるので使ったTSQLを文字で紹介

DECLARE @RefString NVARCHAR(100);
SET @RefString = '関数処理AAA'

RAISERROR ('01 TEST ERROR %3.1s発生', -- メッセージID.
10, -- 重大度,
1, -- State
　@RefString); 　　　　 -- %s に入る文字

RAISERROR ('02 TEST X ERROR %3.2s発生', -- メッセージID.
10, -- 重大度,
1, -- State
　@RefString); 　　　　 -- %s に入る文字

RAISERROR ('03 TEST X ERROR %3.3s発生', -- メッセージID.
10, -- 重大度,
1, -- State
　@RefString); 　　　　 -- %s に入る文字

RAISERROR ('04 TEST X ERROR %10.9s発生', -- メッセージID.
10, -- 重大度,
1, -- State
　@RefString); 　　　　 -- %s に入る文字

RAISERROR ('05 TEST X ERROR %10.6s発生', -- メッセージID.
10, -- 重大度,
1, -- State
　@RefString); 　　　　 -- %s に入る文字

RAISERROR ('06 TEST X ERROR %6s発生', -- メッセージID.
10, -- 重大度,
1, -- State
　@RefString); 　　　　 -- %s に入る文字

結果の表示


2. severity (重大度)とは
Helpから

0 から 18 までの重大度レベルはどのユーザーでも指定できます。19 から 25 までの重大度レベルは、固定サーバー ロール sysadmin のメンバ、または ALTER TRACE 権限のあるユーザーのみが指定できます。重大度レベル 19 から 25 までは、WITH LOG オプションを必要とします。

ユーザが設定する場合0-18までの値を自由に設定できますが、TRY CATCH を使ったERROR処理を実装する場合、重大度10以下ではCATCH ブロックに処理が渡されないので、基本的に11 - 18までとなる。

上記のテストでは重大度を10としてERRORとならず結果を表示するように指定していたが重大度を18とすると以下のようになる

DECLARE @RefString NVARCHAR(100);
SET @RefString = '関数処理AAA'

RAISERROR ('01 TEST ERROR %3.1s発生', -- メッセージID.
18, -- 重大度,
1, -- State
@RefString); -- %s に入る文字

上記の結果

3. state とは
Helpから

1 〜 127 の任意の整数です。state に負の値が設定されている場合は、既定値が 1 になります。0 または 127 より大きい値に設定されている場合は、エラーが発生します。
同じユーザー定義エラーが複数の場所で発生する場合、それぞれの場所に対して一意の状態番号を使用すると、コードのどのセクションでエラーが発生しているのかを探すのに役立ちます。

簡単にいえば処理番号をストアドに書いておき該当する処理でRAISERROR が発生した場合に処理を検証して特定する際に役立つ任意の数値として使うことができる。

TRY CATCH を用いたストアドプロシージャを想定した例

DECLARE @StateNum INT
DECLARE @RefString NVARCHAR(100);

    • //初期設定

SET @StateNum = 1
SET @RefString = 'XXXXXXX'

BEGIN TRY

SET @RefString = '関数処理01'
/*-----------------------------------------------
関数処理01*/

SET @StateNum = 2
/*-----------------------------------------------
関数処理02*/

SET @StateNum = 3
/*-----------------------------------------------
関数処理03 ここでエラー*/
RAISERROR ('ERROR %10.6s発生', -- メッセージID.
18, -- 重大度,
@StateNum, -- State
　@RefString -- %s に入る文字
);

END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

/* CATCH したエラーをCATCH ステートメント内部で再度RAISERRORすることで
　 アプリケーションにエラーとして戻すことができる*/
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);

END CATCH

上記の結果表示
※TRY CATCH を使うことによりTRYブロック内にてRAISERROR 関数のSeverity(11-18)でエラー発生させている。
　そこでアプリケーションにエラーを戻させるため、再度CATCHブロック内でRAISERRORを使っている

CATCH ブロック内のRAISERROR なしの場合は「正常」という処理で終了する。

◇まとめ
RAISERROR 関数の使い方について述べてきた、有効にオリジナルな使い方が考えられる便利な関数であり、TRY CATCH と同様ストアドプロシージャの作成に生かしてもらいたい。

【SQL Server 2005 - ストアド】便利な TRY - CATCH

SQL Server

.Net でおなじみのTRY - CATCH と同様な機能がSQL Server 2005からTSQLに追加された。
TSQL ではBEGIN TRY - END TRY / BEGIN CATCH - END CATCH という記述で表現する。
今回はストアドプロシージャを作成するときに多く使われているこのTRY - CATCH の機能と特徴などについて書いてみる。

◇基本的な構文の形式

BEGIN TRY

(処理ロジック)

END TRY
BEGIN CATCH

(ERROR 処理ロジック)

END CATCH

留意事項

  1. TRY CATCH 構造は一つのストアド、トリガなどに含めなければならず、CATCHだけ別のバッチ処理にいれるということはできない
  2. TRY ブロックの直後に CATCH ブロックを書く
  3. ERROR には「重大度」が判定され、重大度10以下は情報メッセージ/警告とみなされTRY - CATCH 処理されない

※重大度判定はあえてここで説明しませんがERROR の重要性を表す数値です。また、ERRORをCATCH した場合エラーメッセージが戻されます。

◇特徴

  1. TRY ブロックでエラーなく処理ロジックが完了した場合、END CATCHステートメント直後の処理が実行される
  2. .Net ではCATCH するERROR EXPRESSION を識別するクラス定義があるがTSQL はない
  3. TRY - CATCH のネストは同じTRYブロック内/CATCH ブロック内に書くことができ、入れ子の中でTRY - CATCH 処理が行われる

TRY - CATCH に対応できないエラーとして「コンパイル時のエラー」や「オブジェクト名の遅延解決によるエラー」があり、これらのエラー時はTSQL が実行されずエラーメッセージだけが戻されるため、CATCH に書いた処理は実行されない。

CATCH が正常に動作する例
SELECT 1/0 を実行すると除算エラーが発生し処理が中断される。このときSELECT 'AAA' 実行後にCATCHされたことがわかる

TSQLが失敗し動作しなかった例
SELECT * FROM AAA 存在しないテーブルをSELECT した場合、TSQLそのものが実行されずエラーメッセージのみが返される

TSQL に実装されたTRY - CATCH を簡単に説明してきた。
テクニカルに使う際にはもっとノウハウがあると思うが、そういった点についても後に触れていこうと思う。