call Stored Procedure with list of values and append the result

[akos_skutovics]

i have a list of IDs 

1000

1001

1002

...

and I want to call a Stored Procedure with each value of that list, appending the results.


I managed to do it with a parameter, but unsure how to interate through the list

[v-yingjl · Accepted solution]

Hi @akos_skutovics ,

You should declare a minID parameter and a maxID parameter first when create the stored procedure in sql server, like this:

CREATE PROCEDURE SSALES @MINID varchar(50),@MAXID varchar(50)
AS
Select * from _Customer Where CustomerID >= @MINID AND CustomerID <= @MAXID
GO

In power query, create two parameters based on your IDList:

Modify your query like this:

let 
    para = (MINID as text,MAXID as text) =>
let
    Source = Sql.Database("xxx", "xxx", [Query="exec SSALES @MINID = '" & MINID & "', @MAXID = '" & MAXID & "'"])
in
    Source
in 
    para

When you enter the MINID and the MAXID, you can get the specific list result:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@akos_skutovics 

What you're trying to do can be very inefficient, especially when the list is long and the procedure that gathers data is not optimized for this workload. Wouldn't it be better to call the procedure once so that it gathers all the data you need in one go and then brings it into Power Query? Then you could easily join the list/table with the data on the right field... or even postpone it altogether?

[Greg_Deckler]

@akos_skutovics Perhaps try using a Table.AddColumn statement to add a column and the column calls the function.