I suggest you split your stored procedure into two procedures that each respectively return a separate table and have those called to different worksheets.
There are a variety of ways to return data to Excel using SQL
Here is a favourite of mine from code by Joshua (you don’t have to use the parameters):
-
Select the Data tab on Excel’s Ribbon, then within the Get Exernal Data group choose the “From other Sources” drop-down. Then Choose “From Microsoft Query”
-
Within “Choose Data Source” pop-up box, select your SQL Server, then hit OK.
-
Close the “Add Tables” popup if necessary.
-
Click on the “SQL” button, or choose View > SQL to open the SQL pop-up editor.
-
Enter the following syntax: {CALL myDatabaseName.dbo.myStoredProc (?, ?, ?)}
For example: {CALL northwind.dbo.spGetMaxCost (?, ?, ?)}
Be sure to include the squiggly braces around the call statement. Each Question Mark (?) indicates a parameter. If your stored procedure calls for more or less parameters, add or subtract question marks as needed.
-
Hit the OK button. A question box should pop-up saying “SQL Query can’t be represented graphically, continue anyway?”, just hit the OK button.
-
You will now be asked for sample parameters for each question mark you included above. Enter valid parameter values for the data you are querying.
-
Once you have entered the last parameter, you should get some results back in Microsoft Query. If they look good, close Microsoft Query.
-
You should now be looking at an “Import Data” pop-up. Click the Properties button, which will bring up the “Connection Properties” pop-up.
-
Select the Definition tab, then select the Parameters button. You should now see a “Parameters” pop-up, where you can connect the parameter to a specific cell.
-
Select Get the value from the following cell, and then connect to an appropriate cell in Excel that will hold your parameter, by clicking the little box with the arrow.
-
If you want the data to refresh every time you change the cell containing the parameter, check the box stating “Refresh automatically when cell value changes”
-
Continue as above for the other parameters. When finished, click OK, to return to the Connection Properties pop-up. Click OK to return to the Import Data pop-up, and click OK again.
-
You should now have some data straight from your stored procedure.
You will end up with connection information similar to:
Connection info
And, if you use parameters from sheet then, for my example,
