multiple table reports from run one stored procedure

Okay, here's how I solved it at my work (I needed to import data from 1 Stored Proc, which provided 2 outputs):

• Considerations for the SQL side:
1. Create a passable variable "@Worksheet" in your Stored Proc. I'm guessing that for you, the start of your stored proc will look something like this:
    

ALTER PROCEDURE mystoredproc

--@TodaysDate datetime, -- Add date or other parameters as needed

@WorkSheet INT -- This parameter will help you "break" each output

 AS
 

1. Break each output section/select statement as follows:
   IF @Worksheet = 1
   BEGIN
   SELECT * FROM yabbadabbadooDatabase
   END
   ELSE IF @Worksheet = 2
   BEGIN
   SELECT * FROM benandjerryDatabase
   END
2. Here's how your exec statement will look like:
• exec mystoredproc 1
• exec mystoredproc 2

• Considerations on the Power BI side (you probably know most of this stuff anyway, based on your comments above):
1. Import SQL Server data
2. Add Server and Database names as needed.
3. Select "Import" under "Data Connectivity mode"
4. Write the execute query for stored proc for only the 1st output:
• exec mystoredproc 1
5. Transform/load the data as needed.
6. Repeat steps 1-5 for 2nd output:
• exec mystoredproc 2

This will create 2 datasets in your Power BI file, which can be updated by clicking the "Refresh" button.

I hope this finally helps you 🙂

I noticed that other answers weren't as useful here either, nor in your original post. Thankfully someone at work showed me it, and it works for me.

hi  Anonymous 

Power BI will not ingest more than one table at once returned from a SP.

Use the filter queries inside your SP to fetch data inside Power BI.

Basically, you will have two separate queries for two tables.

Regards,

Lin