cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akos_skutovics
Frequent Visitor

call Stored Procedure with list of values and append the result

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

akos_skutovics_0-1619696328601.png

 




1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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:

IDlist.pngMIN.pngMAX.png

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:

rr.png

 

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.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

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:

IDlist.pngMIN.pngMAX.png

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:

rr.png

 

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.

View solution in original post

daxer
Solution Sage
Solution Sage

@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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors