Forum Discussion

davidz106's avatar
davidz106
Helper III
4 years ago

Passing a parameter to SQL stored procedure - LOOP

I am calling SQL stored procedure: let source= Sql.Database("server", "database", [Query="EXEC procOderBySampleID ID = '"&sample&"'"]), sample = #"sample input",   and in parameter "sample input...
Need Help
artpil's avatar
artpil
Resolver II
4 years ago

Hi,

 

I think Rohit's solustion using sql in clause is better you may think of another solution

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkgsSsw1VIrVgTKNEExjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Params = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "EXEC procOderBySampleID ID = '"&[Params]&"'"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Query", each Sql.Database("server", "database", [Query=[Custom]]))
in
    #"Added Custom1"

 

In this solution you will prepare sql statement for each parameter and run separate query for each parameter. Please remember that you will run multiple queries in parallel. This approach is no good if running query against ms access database.

 

Artur