Comment puis-je utiliser des paramètres facultatifs dans une procédure stockée T-SQL ?

Question

Je suis en train de créer une procédure stockée pour effectuer une recherche dans une table. J'ai de nombreux champs de recherche différents, qui sont tous facultatifs. Existe-t-il un moyen de créer une procédure stockée qui puisse gérer cela ? Disons que j'ai une table avec quatre champs : ID, FirstName, LastName et Title. Je pourrais faire quelque chose comme ceci :

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

Ça marche en quelque sorte. Cependant, elle ignore les enregistrements où FirstName, LastName ou Title sont NULL. Si le titre n'est pas spécifié dans les paramètres de recherche, je veux inclure les enregistrements où le titre est NULL - de même pour le prénom et le nom. Je sais que je pourrais probablement le faire avec du SQL dynamique, mais je voudrais éviter cela. Avez-vous des idées ?

Merci, Corey

Answer 1

Modifier dynamiquement les recherches en fonction des paramètres donnés est un sujet compliqué et le faire d'une manière plutôt que d'une autre, même avec une très légère différence, peut avoir des répercussions massives sur les performances. La clé est d'utiliser un index, d'ignorer le code compact, d'ignorer le souci de répéter le code, vous devez faire un bon plan d'exécution de la requête (utiliser un index).

Lisez ceci et considérez toutes les méthodes. La meilleure méthode dépendra de vos paramètres, de vos données, de votre schéma et de votre utilisation réelle :

Conditions de recherche dynamiques en T-SQL par Erland Sommarskog

La malédiction et les bénédictions de SQL dynamique par Erland Sommarskog

Si vous disposez de la version appropriée de SQL Server 2008 (SQL 2008 SP1 CU5 (10.0.2746) et ultérieure), vous pouvez utiliser cette petite astuce pour utiliser réellement un index :

Ajouter OPTION (RECOMPILE) sur votre requête, voir l'article d'Erland et le serveur SQL résoudra le problème OR de l'intérieur (@LastName IS NULL OR LastName= @LastName) avant que le plan de requête soit créé sur la base des valeurs d'exécution des variables locales, et un index peut être utilisé.

Cela fonctionnera pour n'importe quelle version de SQL Server (retournera des résultats corrects), mais n'incluez l'OPTION(RECOMPILE) que si vous êtes sur SQL 2008 SP1 CU5 (10.0.2746) et plus. L'OPTION(RECOMPILE) recompilera votre requête, mais la version indiquée la recompilera sur la base des valeurs actuelles des variables locales au moment de l'exécution, ce qui vous donnera les meilleures performances. Si vous n'utilisez pas cette version de SQL Server 2008, laissez simplement cette ligne vide.

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

Answer 2

Vous pouvez le faire dans le cas suivant,

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
        (@LastNameName IS NULL OR LastName = @LastName) AND
        (@Title IS NULL OR Title = @Title)
END

mais dépendent parfois des données, il vaut mieux créer des requêtes dynamiques et les exécuter.

Answer 3

Étendez votre WHERE condition :

WHERE
    (FirstName = ISNULL(@FirstName, FirstName)
    OR COALESCE(@FirstName, FirstName, '') = '')
AND (LastName = ISNULL(@LastName, LastName)
    OR COALESCE(@LastName, LastName, '') = '')
AND (Title = ISNULL(@Title, Title)
    OR COALESCE(@Title, Title, '') = '')

c'est-à-dire combiner différents cas avec des conditions booléennes.

Answer 4

Cela fonctionne aussi :

    ...
    WHERE
        (FirstName IS NULL OR FirstName = ISNULL(@FirstName, FirstName)) AND
        (LastName IS NULL OR LastName = ISNULL(@LastName, LastName)) AND
        (Title IS NULL OR Title = ISNULL(@Title, Title))