Forum Discussion

bkoenen's avatar
bkoenen
Helper I
3 years ago

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

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 ...
Need Help
ThxAlot's avatar
ThxAlot
Super User
3 years ago

 

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

 

bkoenen's avatar
bkoenen
Helper I
3 years ago

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"