SQL SERVER - TRANSMISSION D'UN PARAMETRE A VALEURS MULTIPLES paramètre, tableau, valeurs, multiples, split, Source N°44698

Information sur la source

Catégorie :Fonctions Classé sous : paramètre, tableau, valeurs, multiples, split Niveau : Débutant Date de création : 13/11/2007 Date de mise à jour : 21/11/2007 11:14:52 Vu : 2 757

Auteur : FENETRES

Ecrire un message privé

Note :
Aucune note

Commentaire sur cette source (5)

Ajouter un commentaire et/ou une note

Description

Cliquez pour voir la capture en taille normale

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

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

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.

Historique

13 novembre 2007 11:25:17 :: Indentation code source
13 novembre 2007 11:48:26 :: Correction faute de frappe (orthographe)
15 novembre 2007 09:50:35 :: Ajout de la fct RTRIM dans la condition d'insertion du dernier item.
15 novembre 2007 09:52:52 :: Indentation
15 novembre 2007 10:03:04 :: Erreur de manip.
15 novembre 2007 10:41:31 :: Jamais deux sans trois
21 novembre 2007 10:34:04 :: MAJ : suppression des items non déclarés
21 novembre 2007 11:14:52 :: Ajout du nota bene en commentaire dans la source

Sources du même auteur

Toutes les sources de FENETRES

Sources de la même categorie

Toutes les sources de la catégorie Fonctions

Commentaires

Commentaire de FENETRES le 13/11/2007 13:42:24

Une autre solution consiste à utiliser le séparateur de chaînes proposé en exemple par Microsoft (Split.vb).
Commentaire de FENETRES le 15/11/2007 10:45:02

Gardez en mémoire que cette solution de simplification provoque une perte de performance (voir le plan d'exécution).
Commentaire de FENETRES le 15/11/2007 15:19:56

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é.
Commentaire de FENETRES le 19/11/2007 15:58:30

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.
Commentaire de FENETRES le 21/11/2007 11:33:01

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.