[解決済み] T-SQLストアドプロシージャでオプションのパラメータを使用するにはどうすればよいですか？

質問

あるテーブルを検索するためのストアドプロシージャを作成しています。 さまざまな検索フィールドがありますが、これらはすべてオプションです。 これを処理するストアドプロシージャを作成する方法はありますか？ 例えば、4つのフィールドを持つテーブルがあるとします。 ID、FirstName、LastName、Titleの4つのフィールドを持つテーブルがあるとします。 このようにすることができます。

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
            FirstName = ISNULL(@FirstName, FirstName) AND
            LastName = ISNULL(@LastName, LastName) AND
            Title = ISNULL(@Title, Title)
    END

これは一応動作します。 しかし、FirstName、LastName、TitleがNULLのレコードは無視されます。 検索パラメータにTitleが指定されていない場合、TitleがNULLであるレコードを含めたいのですが、FirstNameとLastNameも同様です。 ダイナミックSQLを使えばできるかもしれませんが、それは避けたいと思います。

解決方法は？

与えられたパラメータに基づいて検索を動的に変更することは複雑な問題で、ある方法をとると、たとえごくわずかな違いであっても、パフォーマンスに大きな影響を与える可能性があります。 重要なのは、インデックスを使うこと、コンパクトなコードを無視すること、コードの繰り返しを気にすること、優れたクエリ実行計画を立てること（インデックスを使うこと）です。

これを読んで、すべての方法を検討してください。 最適な方法は、パラメータ、データ、スキーマ、実際の使用方法によって異なります。

T-SQLの動的検索条件 by Erland Sommarskog

ダイナミックSQLの呪いと恵み by Erland Sommarskog

SQL Server 2008 の適切なバージョン（SQL 2008 SP1 CU5 (10.0.2746) 以降）であれば、この小技を使って実際にインデックスを使用することが可能です。

追加 OPTION (RECOMPILE) をクエリに追加します。 Erlandの記事参照 を解決し、SQL Server は OR の中から (@LastName IS NULL OR LastName= @LastName) の前に、ローカル変数の実行時値に基づいてクエリプランが作成され、インデックスを使用することができます。

これは、どの SQL Server バージョンでも動作しますが（適切な結果を返します）、SQL 2008 SP1 CU5 (10.0.2746) 以降の場合は OPTION(RECOMPILE) を含めるだけにしてください。 OPTION(RECOMPILE)はクエリを再コンパイルしますが、記載されているバージョンだけがローカル変数の現在のランタイム値に基づいて再コンパイルされ、最高のパフォーマンスを得ることができます。 SQL Server 2008のそのバージョンでない場合は、この行をオフにするだけです。

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))
        OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
    END