Passing a parameter to SQL stored procedure - LOOP

[davidz106]

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

"546-864" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

This works well if I change the text value (546-864) manually. However, I cannot feed list of IDs to my procedure since it only accepts one value at the time. I don't have rights to change the SQL procedure since this is under IT department juristiction. 

Can you help me how to write a loop that goes through a list of values (i.e. another query) and returns value for each value in a list? I was able to do it in VBA with feeding a column of values but I lack M knowledge. 

[artpil]

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

[v-kkf-msft]

Hi @davidz106 ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @davidz106 ,

We can't automatically generate a separate query for each ID. I can do is add a custom column to the ID list and call stored procedure for each ID in this column.

= Sql.Database(".", "CaseTest", [Query="EXEC GetProductDesc_withparameters @PID = '" & [ID List] & "'"])

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rohit_singh]

Hi @davidz106 ,

I can demonstrate a very simple of how something like this can be done using an example.
I'm using the Adventureworks 2019 database as my source. The basic idea is to create a list of values from a 2nd query that can be used as a parameter in your stored procedure.

I have two queries :

1) I run a simple select statement on table SalesLT.Address to filter out addresses that exist only in Washington or Ontario. You will notice Washington and Ontario hardcoded in the query.

2) Next, I want to use another query to give us a list of values that dyamically replace the hardcoded values above. 
I create a simple table with 2 values, Washington and Ontario. Then I perform a series of transformations to create a list of values. 

Please copy and paste this code into a blank query to see the steps being perfomed.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk8szsjMSy/Jz1OK1YlW8s8rSSzKzFeKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Places = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Places", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Places", each "'" & _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Places", each _ & "'", type text}}),
    #"Grouped Rows" = Table.Group(#"Added Suffix", {}, {{"Count", each _, type table [Places=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Places]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), " , "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    Custom = #"Removed Columns"[Custom]{0}
in
    Custom

The end result of this query is that I get a list of values in the form 

Next, I replace tyhe hardcoded values with my parameter. I replace 'Washington' , 'Ontario' with "&Location&" in my query as shown below. I grant permissions andclick on okay to execute, and the query executes successfully with a list of values as parameter.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂