Forum Discussion

pzawodzinski's avatar
pzawodzinski
Copper Contributor
Jun 12, 2020

Excel mysql pass parameters

Hi.

I try connect exel to mssql serwer. Connections work great except WHERE clause.

This is my simple query :

SELECT * FROM EMPLOYERS WHERE ID > 100

How I can pass where condition in this case 100 from exel cell ?

Til now work "?" and then exel ask where is parameters but now this method stops working.

I found this :

 

 

let
    Źródło = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"ID", Int64.Type}}),
    IDFirmy = #"Zmieniono typ"{0}[ID],


    Źródło2 = Sql.Database("localhost\sqlexpress2019", "SZKOLENIE", [Query="SELECT * FROM EMPLOYERS WHERE ID > 100"]),
    #"Przefiltrowano wiersze" = Table.SelectRows(Źródło2, each ([ID] = IDFirmy ))
in
    #"Przefiltrowano wiersze"

 

 

 

work ok but filtering is perform in exel not in sql so performance may be low.

excel

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 13, 2020

    pzawodzinski 

    If ID in some named cell as here

    you may use it in query like

    let
    GetID = Excel.CurrentWorkbook(){[Name="ID"]}[Content][Column1]{0},
    queryString="SELECT * FROM EMPLOYERS WHERE ID > " & Text.From(GetID)
    //Źródło2 = Sql.Database("localhost\sqlexpress2019", "SZKOLENIE", [Query=queryString])
in
    queryString