Passing a parameter to SQL stored procedure - LOOP

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