Sql server - transmission d'un parametre a valeurs multiples

Soyez le premier à donner votre avis sur cette source.

Snippet vu 25 518 fois - Téléchargée 18 fois

Commenter

Contenu du snippet

Il est souvent nécessaire de transmettre un nombre variable de paramètres à une procédure stockée. Cette source propose une alternative à la création d'une table temporaire.

Source / Exemple : 


CREATE FUNCTION [dbo].[fctSplitToInt]( 
    @DelimitedText VARCHAR(MAX),  -- liste CSV
    @QuoteChar CHAR(1)            -- caractère de délimitation
) RETURNS @Items TABLE (Item INTEGER)
/* -- Retourne la liste sous la forme d'une colonne de nombres 
    -- Nota bene : les items non déclarés (chaînes vides ou ne comprenant que le caractère d'espacement) sont ignorés
    -- Cette fonction permet en particulier de transmettre un paramètre « multivalué ».
    -- Exemple d'utilisation :
    USE AdventureWorks;
    DECLARE @ListID VARCHAR(MAX);
    -- Déclaration des valeurs multiples du paramètre
    SET @ListID= '16, 21, 148, 173, 184, 185';
    -- Table retournée
    SELECT Item FROM [dbo].[fctSplitToInt](@ListID, ',');
    -- Transmission du paramètre à valeurs multiples pour filtrer une requête de sélection
    SELECT Title FROM HumanResources.Employee WHERE ManagerID IN (SELECT * FROM [dbo].[fctSplitToInt](@ListID, ','));


  • /

BEGIN
   DECLARE @Item VARCHAR(11)
   WHILE CHARINDEX(@QuoteChar, @DelimitedText, 0) <> 0 BEGIN
        SELECT @Item=SUBSTRING(@DelimitedText,1,CHARINDEX(@QuoteChar,@DelimitedText, 0)-1), 
               @DelimitedText=SUBSTRING(@DelimitedText,CHARINDEX(@QuoteChar,@DelimitedText, 0) + LEN(@QuoteChar), LEN(@DelimitedText))
        IF LEN(RTRIM(@Item)) > 0
            INSERT INTO @Items SELECT CONVERT(INTEGER, @Item)
   END
   -- Dernier item de la liste
   IF LEN(RTRIM(@DelimitedText)) > 0
       INSERT INTO @Items SELECT CONVERT(INTEGER, @DelimitedText)
   RETURN
END

Conclusion :


L'exemple en commentaire dans le code source permet de filtrer une requête de sélection de façon dynamique (paramètre à valeurs multiples) grâce à la fonction table.

A voir également

Ajouter un commentaire Commentaires
FENETRES Messages postés 196 Date d'inscription jeudi 15 juillet 2004 Statut Membre Dernière intervention 14 avril 2009
13 nov. 2007 à 13:42
Une autre solution consiste à utiliser le séparateur de chaînes proposé en exemple par Microsoft (Split.vb).
FENETRES Messages postés 196 Date d'inscription jeudi 15 juillet 2004 Statut Membre Dernière intervention 14 avril 2009
15 nov. 2007 à 10:45
Gardez en mémoire que cette solution de simplification provoque une perte de performance (voir le plan d'exécution).
FENETRES Messages postés 196 Date d'inscription jeudi 15 juillet 2004 Statut Membre Dernière intervention 14 avril 2009
15 nov. 2007 à 15:19
J'ai repris l'expression utilisée par SQL Server Reporting pour qualifié le paramètre bien que l'épithète « multi-valué » me semble plus approprié.
FENETRES Messages postés 196 Date d'inscription jeudi 15 juillet 2004 Statut Membre Dernière intervention 14 avril 2009
19 nov. 2007 à 15:58
En dernier lieu, rappelez vous que la conversion d'une chaîne vide en entier retourne zéro. Par conséquent,l'instruction SELECT * FROM [dbo].[fctSplitToInt]('10;;20', ';') retournera dans l'ordre les nombres 10, 0 et 20.
FENETRES Messages postés 196 Date d'inscription jeudi 15 juillet 2004 Statut Membre Dernière intervention 14 avril 2009
21 nov. 2007 à 11:33
Le code source a été mis à jour pour gérer l'éventualité précédemment évoquée. Par suite,l'instruction SELECT * FROM [dbo].[fctSplitToInt]('10;;20', ';') renverra maintenant seulement les nombres 10 et 20.
S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (FENETRES)