166 votes

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

Je suis entrain de créer une procédure stockée pour faire une recherche dans un tableau. J'ai beaucoup de différents champs de recherche, qui sont toutes optionnelles. Est-il un moyen de créer une procédure stockée qui va gérer cela? Disons que j'ai une table avec quatre champs: ID, Prenom, Nom et Titre. 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

Ce genre de travaux. Mais il ignore les enregistrements où le Prénom, le Nom ou le Titre sont NULLES. Si le Titre n'est pas spécifié dans les paramètres de recherche que je veux inclure les enregistrements où le Titre est NUL - même pour FirstName et LastName. Je sais que je pourrais probablement le faire avec SQL dynamique, mais je voudrais l'éviter. Des idées?

Merci, Corey

237voto

KM. Points 51800

De manière dynamique l'évolution des recherches basées sur les paramètres est un sujet complexe et de le faire d'une façon plutôt qu'une autre, même avec seulement une très légère différence, peut ont d'énormes implications sur les performances. La clé est d'utiliser un index, ignorer code compact, ignorer se soucier de la répétition du code, vous devez faire un bon plan d'exécution de requête (utilisation d'un index).

Lire le présent et d'envisager toutes les méthodes. Votre meilleure méthode dépend de vos paramètres, vos données, votre schéma, et votre utilisation actuelle:

Dynamique des Conditions de Recherche en T-SQL, par Erland Sommarskog

La Malédiction et la bénédiction de SQL Dynamique par Erland Sommarskog

Si vous avez la bonne version de SQL Server 2008 SQL (2008 SP1-5 UM (10.0.2746) et plus tard), vous pouvez utiliser ce petit truc en fait l'utilisation d'un index:

Ajouter OPTION (RECOMPILE) sur votre requête, voir Erland de l'article, et SQL Server résoudre l' OR de dans les (@LastName IS NULL OR LastName= @LastName) avant le plan de requête est basée sur les valeurs de l'exécution des variables locales, et un index peut être utilisé.

Cela fonctionnera pour n'importe quelle version de SQL Server (retour d'un bon résultat), mais seulement inclure l'OPTION(RECOMPILER) si vous êtes sur SQL server 2008 SP1-5 UM (10.0.2746) et plus tard. L'OPTION(RECOMPILER) est de recompiler votre requête, seule la verison sera le recompiler en vigueur en fonction du moment de l'exécution valeurs des variables locales, qui vous donnera les meilleures performances. Si ce n'est que la version de SQL Server 2008, il suffit de laisser cette ligne hors tension.

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

21voto

Michael Pakhantsov Points 11344

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

cependant, dépendent de données parfois mieux créer une dynamique de requêtes et de les exécuter.

7voto

devio Points 22981

Prolonger votre WHERE condition de:

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, '') = '')

j'. e. combiner les différents cas avec l'opérateur booléen conditions.

-3voto

v2h Points 1

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))

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by: