Power Query Passer une liste de paramètres à une procédure stockée
Avec Power Query Passer une liste de paramètres à une procédure stockée est finalement assez facile.
Une procédure stockée est en fait une série d’instructions SQL désignée par un nom. Lorsque l’on crée une procédure stockée, on l’enregistre dans la base de données que l’on utilise, au même titre qu’une table par exemple. Une fois la procédure créée, il est possible d’appeler celle-ci, par son nom. Les instructions de la procédure sont alors exécutées.
L’objectif de cet article est de vous montrer comment passer une liste de paramètres, une table Excel notamment et non un cours sur les procédures stockées aussi je vais prendre un exemple très simple.
Je vais transformer l’instruction SQL suivante en procédure stockée.
SELECT *
FROM [FruitsDemo].[dbo].[LigneFacture]
WHERE [ProduitId] in ( 'LF-AIL120' , 'LF-AIL100')
Cette requête SQL récupère les lignes de ma table facture, mais seulement pour les produits ‘LF-AIL120’ , ‘LF-AIL100’, soit ici 2 produits. Mon objectif est de pouvoir remplacer dynamiquement ces deux produits par une liste de produits saisis dans une table Excel.
Je vais transformer cette instruction en procédure stockée, dans SSMS je tape les lignes suivantes :
Create Procedure dbo.ListeFruit
@OrderList varchar(500)
AS
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT * FROM [FruitsDemo].[dbo].[LigneFacture] '
Select @SQL = @SQL + 'WHERE [ProduitId] in (' + @OrderList +')'
Exec ( @SQL)
GO
Je sélectionne ces lignes et je clique sur F5 pour lancer la création de ma procédure stockée.
Je peux maintenant l’utiliser en tapant l’instruction suivante :
Exec ListeFRuit @OrderList = "'LF-AIL120' , 'LF-AIL100'"
Et j’obtiens le même résultat.
Ce qu’il faut remarquer, c’est que les paramètres sont chacun entourés d’une simple-quote, séparés par une virgule, et l’ensemble des paramètres sont eux entourés de double-quote, ce qu’il faudra reproduire dans Power Query.
La requête dans Power Query va se passer en 2 étapes, une première va consister à récupérer la liste de paramètres de ma table Excel et à la structure dans un format acceptable par la procédure stockée, puis dans un deuxième temps de lancer ma procédure stockée avec Power Query.
Il y a ceux qui sont pressés de voir le langage M de la requête finale et ceux qui veulent voir en détail comment on s’y prend, donc pour les deuxièmes une vidéo et les autres le code.
La vidéo
Le code
let
Source = Excel.CurrentWorkbook(){[Name="Fruits"]}[Content],
#"Personnalisée ajoutée" = Table.AddColumn(Source, "Custom", each "'"&[Fruits]&"'"),
#"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisée ajoutée",{"Fruits"}),
#"Colonnes renommées" = Table.RenameColumns(#"Colonnes supprimées",{{"Custom", "Fruits"}}),
Liste = Table.ToList(#"Colonnes renommées"),
ListeVirgule = Lines.ToText(Liste,","),
ListeParamDebut=Text.Start(ListeVirgule,Text.Length(ListeVirgule)-1),
ListeParam = """"&ListeParamDebut&"""",
RequeteSql = Sql.Database("BI", "FruitsDemo", [Query="Exec ListeFRuit @OrderList = " & ""&ListeParam])
in
RequeteSql
