Stored SQL Procedure with 3 parameters get the values from another table in same Excel file

[bkoenen]

Hello,

I want to Execute a stored SQL Procedure in Excel and get the Parameters from an existing table in the same Excel book, so that users can fill in the parameters.

The stored procedure works fine when I fill in the parameters like this:
= Sql.Database("SQL01", "640", [Query="EXECUTE DBO._sp_memoriaal '2023', '1', '6'"])

I would like to fill in the parameters from the Parameters table which have 3 columns.

So the code should be something like this so it gets the values from the Parameters table:

= Sql.Database("SQL01", "640", [Query="EXECUTE DBO._sp_memoriaal, Table.Column(Parameters, 'Jaar', 'VanPeriode', 'TmPeriode')"])

But I don't know what the correct code should be to get this done?

Kinde regards,

Björn

[ThxAlot]

= Sql.Database("SQL01", "640", [Query="EXEC [dbo].[_sp_memoriaal] '" & Text.Combine(List.Transform(Table.ToRows(Parameters){0},Text.From),"','") & "'"])

[bkoenen]

Hello,

Thank you for the solution, it works fine 👍

Just one thing, in the Workbook I use a sheet where the users can fill the Parameters (Table2) that are used in the Query for the stored procedure. When I change the cell value C2 in value "4", and then refresh everything, it changes the value back into the current value "6".  How to solve this that it takes the value "4"?

Parameters code:

let
    Bron = Excel.CurrentWorkbook(){[Name="Tabel2"]}[Content],
    #"Type gewijzigd1" = Table.TransformColumnTypes(Bron,{{"Kolom2", Int64.Type}, {"Kolom1", type text}}),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Type gewijzigd1",{{"Kolom1", type text}, {"Kolom2", Int64.Type}}),
    #"Gedraaide kolom" = Table.Pivot(#"Type gewijzigd", List.Distinct(#"Type gewijzigd"[Kolom1]), "Kolom1", "Kolom2")
in
    #"Gedraaide kolom"