SQL dynamique dans une fonction table

Version imprimable

Bonjour,
je dois fournir une requetes à plusieurs de mes clients pour un reporting adhoc (qui va être effectué via PowerBI et un cube SSAS Tabulaire => donc je dois avoir un modèle générique et standard qui pourra être déployer chez tous les clients), certaines tables contienne des colonnes qui sont ajoutés à la volé au niveau du paramétrage de l'ERP donc impossible de savoir les colonnes qui sont ajoutés sur chaque base de données.
je peux avoir les colonnes qui ont été ajouté à partir d'une table de paramétrage. j'ai développé donc une requête pour retourner les 10 premières colonnes de chaque table.
- j'ai pensé à mettre ma requete dans une fonction table pour retourner une variable table avec mais malheureusement les udf n'accèptent pas le sql dynamique
- les tables peuvent avoir de 0 à 69 colonnes dynamique et je vais prendre seulement les 10 premières colonnes donc un petit problème pour les tables qui contiennent un nombre de colonne < 10
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 CREATE FUNCTION reporting.udfInfoLibre ( @file NVARCHAR(MAX) ) RETURNS @Libre TABLE ( CT_Num VARCHAR(10), Info1 VARCHAR(69), Info2 VARCHAR(69), Info3 VARCHAR(69) --, --Info4 varchar(69), --Info5 varchar(69), --Info6 varchar(69), --Info7 varchar(69), --Info8 varchar(69), --Info9 varchar(69), --Info10 varchar(69) ) AS BEGIN DECLARE @col NVARCHAR(MAX); DECLARE @sql NVARCHAR(MAX); SELECT @col = COALESCE(@col + ',[' + CAST(CB_Name AS VARCHAR) + ']', '[' + CAST(CB_Name AS VARCHAR) + ']') + ' as ' + t.alias FROM ( SELECT CB_Name AS CB_Name, 'Info' + CAST(ROW_NUMBER() OVER( ORDER BY CB_Pos) AS VARCHAR(3)) AS alias FROM cbSysLibre WHERE CB_File = @file AND CB_Pos <= 10 ) AS t; SET @sql = ' SELECT CT_Num ,' + @col + ' FROM F_COMPTET WHERE CT_Type = 0'; EXEC sp_executesql @sql; RETURN; END;

07/03/2019, 22h43
i.chafai

je pense que la meilleure et la plus simple solution et d'utiliser mon script dynamique dans une procédure stocké, et cette dernière elle va me créé un view avec le nom et les alias de colonnes que je définie dans le code.
de toute les colonnes seront et situation quasi-statique après la phase du paramétrage de l'ERP, et en cas de besoin je re-execute la proc

Je me trouve juste bloqué au niveau du nombre de colonne, dans mon cas la table des clients contient uniquement 3 colonnes libres et j'ai besoin de créer une view avec 10 colonnes

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
 
IF OBJECT_ID('reporting.vInfoLibreCient', 'v') IS NOT NULL
    DROP VIEW reporting.vInfoLibreCient;
DECLARE @col NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
SELECT @col = COALESCE(@col + ',[' + CAST(CB_Name AS VARCHAR) + ']', '[' + CAST(CB_Name AS VARCHAR) + ']') + ' as ' + t.alias
FROM
(
    SELECT CB_Name AS CB_Name, 
           'Info' + CAST(ROW_NUMBER() OVER(
           ORDER BY CB_Pos) AS VARCHAR(3)) AS alias
    FROM cbSysLibre
    WHERE CB_File = 'F_Comptet'
          AND CB_Pos <= 10
) AS t;
SET @sql = 'CREATE VIEW reporting.vInfoLibreCient (CT_Num,info1,info2,info3,info4) as  SELECT CT_Num ,' + @col + ' FROM F_COMPTET WHERE CT_Type = 0';
EXEC (@sql);

l'erreur bien sur :

Code:

Msg 8159, Niveau 16, État 1, Procédure vInfoLibreCient, Ligne 1'vInfoLibreCient' a moins de colonnes que celles spécifiées dans la liste des colonnes.

J'ai trouvé une solution à mon besoin mais surement il y aura une grande optimisation à faire

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
ALTER PROCEDURE reporting.InstallInfoLibreCLIAS
    BEGIN
        SET NOCOUNT ON;
        IF OBJECT_ID('reporting.vInfoLibreCient', 'v') IS NOT NULL
            DROP VIEW reporting.vInfoLibreCient;
        DECLARE @col NVARCHAR(MAX);
        DECLARE @sql NVARCHAR(MAX);
        WITH ml
             AS (SELECT '1' AS CB_Name, 
                        1 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        2 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        3 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        4 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        5 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        6 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        7 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        8 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        9 AS CB_Pos, 
                        'F_Comptet' AS CB_File
                 UNION
                 SELECT '1' AS CB_Name, 
                        10 AS CB_Pos, 
                        'F_Comptet' AS CB_File)
             SELECT @col = COALESCE(@col + ',' + CAST(CB_Name AS VARCHAR) + '', '' + CAST(CB_Name AS VARCHAR) + '') + ' as ' + t.alias
             FROM
             (
                 SELECT ISNULL(QUOTENAME(s.CB_Name),'''''') AS CB_Name, 
                        'Info' + CAST(ROW_NUMBER() OVER(
                        ORDER BY ISNULL(s.CB_Pos, ml.CB_Pos)) AS VARCHAR(3)) AS alias
                 FROM cbSysLibre s
                      RIGHT JOIN ml ON s.CB_Pos = ml.CB_Pos
                                       AND s.CB_File = ml.CB_File
             ) AS t;
        SET @sql = 'CREATE VIEW reporting.vInfoLibreCient (CT_Num,info1,info2,info3,info4,info5,info6,info7,info8,info9,info10) as  SELECT CT_Num ,' + @col + ' FROM F_COMPTET WHERE CT_Type = 0';
         EXEC (@sql);
        --PRINT @sql
    END;