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 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

Need Help

2 Replies

  • 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"