クエリアナライザによるストアドプロシージャのデバッグテクニック / 15 seconds

はじめに

　SQLステートメントのデバッグは、Visual Studio .NET環境でやるよりもSQL Server 2000のクエリアナライザでやった方がよいことがあります。私自身、バグがSQLストアドプロシージャにあることがわかっているときは、デバッグにSQLクエリアナライザを使うことがほとんどです。問題の所在がSQLだとわかっているのに、わざわざVisual Studioのようなオーバーヘッドの大きなアプリケーション開発環境を使っても、メリットはありません。問題を手早く突き止めるためには、呼び出し側アプリケーションを省いてマシンに余計な負荷をかけないようにする、というのも有益なテクニックです。ただしそのためには、使用している権限を偽造もしくはバイパスできること――つまり、セキュリティコンテキストを呼び出し側アプリケーションの元の状態（バグのある状態）と同じにできること――が前提となります。

サーバー/クライアント/ネットワークのセットアップ

　使用するWindowsオペレーティングシステムやSQL Serverのバージョン、インストールしてあるサービスパックなどによっては、デバッグが一筋縄ではいかないこともあります。また、サーバー上でのデバッグなら1台のマシンをチェックするだけで済みますが、クライアント上でのデバッグだと、クライアントとサーバーの両方がインストールされ、正しく設定されていることを確認しなければなりません。さらに、単純なLAN以外のものを使用しているときは、PRC設定やファイアウォール設定も含め、ネットワーク問題のチェックも必要となります。こうした問題のトラブルシューティングには、Microsoftのサポート技術情報の文書番号280101『INF - Transact-SQL Debugger Limitations and Troubleshooting Tips for SQL Server 2000』が役に立ちます。

　また、端末/リモートセッションを通じてのデバッグは当面できません。Microsoftによるバグの解決待ちです。これについては、文書番号280100『 BUG - Transact-SQL Debugger Is Not Available Through Terminal Server Session』を参照してください。

セキュリティ/権限

　ローカルマシンでデバッグするためには、クエリアナライザへのログオンに使用するユーザアカウントが、masterデータベースにあるsp_sdidebug拡張ストアドプロシージャの実行権限を持っている必要があります。その他のSQL権限や認証がすべて引き続き有効であることを確認し、そうでないときは、バグを生じたユーザアカウント以外のユーザアカウントをデバッグに使用してください。

とにかくやってみる

　上記の設定その他は、いかにも面倒そうに聞こえるかもしれません。Visual Studioからのデバッグでは、おそらく、そうした面倒な作業の全部またはほとんどが既に済んでいて、だからこそVisual Studioの内部からデバッグできるわけです。SQL Serverのデバッグ設定をまったくやっていない方は、まずストアドプロシージャのデバッグをやってみてください。それなら最初のトライから成功するかもしれません。

デバッガを探す

　SQL 2000のクエリアナライザには、「オブジェクトブラウザ」と呼ばれるツリー表示機能があり、接続先のSQL Serverにあるすべてのものを表示できます。オブジェクトブラウザをまだ使ったことがない方は、［Tools］→［Object Browser］→［Show/Hide］メニューオプションを選択して表示してください。

　オブジェクトブラウザのツリー表示が現れたら、データベースからストアドプロシージャを選び、そのストアドプロシージャ名を右クリックします。右クリックメニューが現れ、一番下に［Debug］オプションがあります。

　このメニューオプションが灰色になっているときは、現ユーザに付与されている権限に不備があります。

例1：単純なストアドプロシージャのデバッグ

　デバッガ機能を試すために、まず次のストアドプロシージャを作成します。

CREATE  procedure DebugA
(
    @IntIn int,
    @IntOut int OUTPUT
)
as

    Set @IntOut = @IntIn + 10

Return

　これは、ある数を受け取り、それに10を加えて、新しい数を出力変数に返すという単純なストアドプロシージャです。このストアドプロシージャは、クエリアナライザを使って次のコードで実行できます。

Declare @NewInt int
exec DebugA 5,@NewInt OUTPUT
Select @NewInt

　この例では、ストアドプロシージャに値5を与えているため、それに10を加えた値15が返されます。

［Debug Procedure］ウィンドウ

　オブジェクトブラウザのツリー表示でストアドプロシージャ名を右クリックし、［Debug］を選択すると、［Debug Procedure］ウィンドウが現れます。ここで各パラメータに初期値を指定できます。

　右下の［Value］ボックスに値を入れてください。［Set to null］チェックボックスをオンにすれば、値をNULLに設定できます。

　出力専用として扱いたいパラメータの場合は、必ず［Set to null］チェックボックスをオンにしてください。

　デバッグセッションの結果をデータベースに反映させたくないときは、［Auto roll back］チェックボックスをオンにしておきます。これで、ストアドプロシージャのデバッグ中にデータベースに加えた変更がすべてロールバックされます。

　初期値の設定が終わったら、［Execute］をクリックして、デバッガに入ります。デバッガは、実行すべき最初のコード行で停止しています。

注

　私は画面を広く使いたいため、デバッグ中はオブジェクトブラウザを閉じています。

デバッガウィンドウ

　デバッガのメインウィンドウの上に、いくつかのツールアイコンが並んでいます。これらのツールアイコンを使用して、どのコード行で実行を一時停止するかや、どのコード部分を細かく分析するかを制御できます。ツールアイコンの下にあるのがメインウィンドウで、ここにストアドプロシージャのコードが表示されています。このコードウィンドウの下に、さらに2つのウィンドウセクションがあります。

　中段のセクションは3つに分かれていて、それぞれにローカル変数、グローバル変数、コールスタックが表示されています。ローカル変数は、デバッグ内部で変更できます。グローバル変数は、デバッグ内部で追加もしくは除去できます。この例では@@rowcountと@@identityという2つの変数を追加しています。どの@@変数がデバッグに役立つかを判断するのには、慣れが必要かもしれません。

　下段のセクションは結果ウィンドウで、クエリアナライザがデバッグモードにないときのウィンドウと同じです。つまり、結果セットやPrintステートメントを使用しているときは、それがここに表示されます。

再び例1へ

　ここまで説明どおりに操作してきた場合は、デバッグウィンドウのローカル変数セクションでは@InIntの値が5、@OutIntの値がNULLにになっているはずです。では、すべてのコード行をreturnステートメントまで1つ1つ実行していきましょう。@OutIntの値が15になるはずです。この例のストアドプロシージャは入れ子ではないので、［Callstack］ウィンドウは無視してかまいません。

例2：入れ子になったストアドプロシージャ

　今度は、［Callstack］ウィンドウの理解を深めるために、T-SQLコードは単純なままで、別のストアドプロシージャを追加することにします。次のコードを実行して、必要なストアドプロシージャを作成してください。

CREATE TABLE [Table1] (
    [TestId] [int] IDENTITY (1, 1) NOT NULL ,
    [A] [int] NOT NULL ,
    [B] [int] NOT NULL ,
    [Total] [int] NOT NULL 
) 

ALTER  procedure DebugA
(
    @IntIn int,
    @IntOut int OUTPUT
)
as

    Declare @Id int

    exec DebugB 
        @IntIn,
        10,
        @IntOut OUTPUT,
        @Id OUTPUT

return

Create procedure DebugB
(
    @IntIn int,
    @IntConst int,
    @Total int OUTPUT,
    @Id int OUTPUT
)
as

    Set @Total = @IntIn + @IntConst

    Insert into Table1
    (A,B,Total)
    values
    (@IntIn,@IntConst,@Total)

    Select @Id = @@identity

Return

　これで「Table1」テーブルが作成され、DebugAストアドプロシージャがもう1つのストアドプロシージャDebugBに入力値を引き渡すように変更されます。DebugBストアドプロシージャは新しいテーブルに1行を挿入し、その行の自動採番ID値（Id）と、入力変数に10を加えた値を返します。この新しい合計値とID値がDebugAストアドプロシージャに戻され、そこから出力変数に返されます。

　コードの実行後、オブジェクトブラウザでDebugAストアドプロシージャを右クリックして、［Debug］を選択してください。最初のパラメータを5、2番目のパラメータをNULLと指定し、［Execute］ボタンをクリックします。

　ローカル変数ウィンドウとグローバル変数ウィンドウには、例1と同じ値が表示されるはずです。デバッグツールバーにある［Step Into］ツール（）をクリックするか、［F11］キーを押してください。

　コードウィンドウに、DebugBストアドプロシージャのコードが表示されます。ローカル変数、グローバル変数、コールスタックの3ウィンドウは、デバッガの新しい状態に合わせてすべて変化します。コール前のデバッガ状態をもう一度見たいときは、コールスタックからDebugAを選択します。

　DebugBのグローバル変数ウィンドウで、@@rowcount変数と@@identity変数を追加します。追加直後の値はゼロのはずです。

　では、最初のステートメントをステップ実行し、@Totalの値を設定してみましょう。@Totalは15、@@rowcountは1となります。次のステートメントをステップ実行すると、これらの値がテーブルに挿入されます。@@transactionが1に増え、@@rowcountの値が1（1行挿入の意味）になり、@@identityにはId列の値が入ります。他に行を追加していなければ、Idは1になっているはずです。

　挿入・更新・削除のいずれかのステートメントにバグがある場合は、当該ステートメント実行後の@@rowcountが0となるため、すぐにそれとわかります。この方法は、Where句が目的の行だけを選択するよう適切に機能しているかどうかを確認するときにも役立ちます。

　次のSelectステートメントをステップ実行すると、@Idフィールドの値が設定されます。これですべての出力値が設定され、プロシージャは実質的に完了です。returnステートメントをステップ実行すると、DebugAに戻ります。

　DebugAでは、出力パラメータ@OutIntの値が15になり、returnステートメントのところで実行が一時停止しているはずです。ローカル変数の値を変更できることの実証として、ローカル変数の@OutIntをクリックし、値を20に変えてみてください。これでreturnステートメントをクリックすると、いま設定したとおり、@OutIntパラメータの値が20になります。

　デバッグモードでローカル変数の値を変更することの意義は何でしょうか？　ストアドプロシージャに含まれる問題がいつも1つとは限りません。1つを修正するたびに初めからやり直していたのでは、時間が無駄になります。最初の問題の所在がわかったら、影響を受けたローカル変数を変更して、すぐ次のバグに移動した方が便利です。

カーソルなどの複雑なコード

　コードが複雑になればなるほど、デバッグのありがたみがわかります。たとえば、カーソルでは実に多くのことが行われているため、デバッグは必須です。いくつものテーブルが関係する入れ子式のカーソルなどは、その最たるものでしょう。SQLクエリアナライザを使わずにデバッグをする場合は、カーソルや入れ子式プロシージャを完全に排除し、そういう複雑な作業を呼び出し側アプリケーションに任せ、そちらのデバッガ（たとえば、Visual Studio）を使いたくなる人もいるかもしれません。SQL Server 2000のデバッガは完璧には程遠い代物ですが、正しい方向への大きな1歩だとは言えるでしょう。このおかげで、T-SQLコードのロジックをあるべきところに置き、コードを強力に管理できるのです。

例3：カーソル

　次のコードでカーソルを作成してみましょう。

create procedure DebugCursor
as

DECLARE cursorDebug Cursor 
FOR 
Select 
    TestId, 
    A,
    B,
    Total
From Table1

Open cursorDebug

Declare @cursorTestId int
Declare @cursorA int
Declare @cursorB int
Declare @cursorTotal int

Declare @randomDifference int



Fetch NEXT FROM 
    cursorDebug 
INTO 
    @cursorTestId, 
    @cursorA, 
    @cursorB,
    @cursorTotal
While (@@FETCH_STATUS =0)
    BEGIN

        -- generate random number seeded by row id
        Set @randomDifference = Convert(int,Rand(@cursorTestId))

        Update Table1 Set
            @cursorTotal = @cursorA + (@cursorB * @randomDifference)
        Where
            TestId = @cursorTestId

    END

    FETCH NEXT From
        cursorDebug 
    INTO 
        @cursorTestId, 
        @cursorA, 
        @cursorB,
        @cursorTotal

CLOSE cursorDebug
DEALLOCATE cursorDebug
GO

　このカーソルは例2の変形です。データベースに挿入される合計値を、行ごとに、その行のIDに基づくランダムなパーセンテージで変更します。

　オブジェクトブラウザでDebugCursorストアドプロシージャを右クリックし、［Debug］をクリックします。このストアドプロシージャには入力パラメータも出力パラメータもないので、設定は不要です。［Execute］ボタンをクリックします。

　これでSQLクエリアナライザのデバッガが呼び出され、実行可能な最初のステートメントが示されます。

　グローバル変数に@@fetch_statusを追加します。@@fetch_statusは、カーソル変数に行が返されたかどうかを示す変数で、Selectステートメントが意図どおりの働きをしてくれているかどうかの目安になります。

　カーソルループ内のステートメントを1つずつステップ実行していき、Setステートメントまで進みます。@randomDifference変数がNULLであることに注意してください。Setステートメントを実行すると、これがあるパーセンテージ値に変わります（float型）。

　次はUpdateステートメントで、B列の値を更新します。新しい値は、Bの旧値に新しいランダム値をかけた値になります。こうして、結果的に、B列の値はどれも他行とは異なる値となり、それにともなって合計も変化します。本稿の説明どおりに操作してきた場合、テーブルには1行しかないので、更新後の@@rowcountは1になるはずです。

　このカーソルがさらに別のストアドプロシージャを呼び出したとしても、そのストアドプロシージャの中までコードと値を追いかけていくことは難しくありません。

まとめ

　SQL Server 2000によるデバッグは、大きな前進です。たとえストアドプロシージャのコードが複雑で、入れ子になっていても、ステートメントごとにデータベースの状態がどう変化するかを確認しながら、テストを進めることができます。この方法ならば、SQL Serverの持つトランザクション能力と処理速度をフルに生かすことができ、同様の機能を呼び出し側のアプリケーションで作り直す必要はなくなります。

著者紹介

Dina Fleet Berry（Dina Fleet Berry）