Adding a Stored Procedure to an Excel Microsoft Query

This may be somewhat technical, but for those of you who utilize Excel quite heavily and are adept at using Microsoft Query to create refreshable reports/lists, you may want to utilize a Stored Procedure that has already been created in Microsoft SQL.

A Stored Procedure is a group of SQL commands that runs to produce some valued output.  For instance there are Stored Procedures that can put information from the Payables Open Tables together with the Payables History Tables to give you a listing of all of the outstanding payables as of a certain point in time.  We routinely refer to this as a Payables Historical Aged Trial Balance.

There may be times where you want to utilize a Stored Procedure that has already been written and return that information to Excel.  Most times, there are also PARAMETERS that need to be included so that the Stored Procedure will return just the set of data you actually want.

In this example we have a Stored Procedure that compiles Payables info based upon a certain point in time– let’s call it SP_PM_AGED_HISTORY (and it already exists within our SQL database).  The trick is, we also need to be able to identify the DATE in for when we want the procedure to run, because without this important piece of data, the Stored Procedure will NOT run.  This is called a PARAMETER.

Utilizing Microsoft Query we can do the following:

1. Open the SQL View
2. {CALL <storedprocedurename>(?)}  In our example it would look like this: {CALL  SP_PM_AGED_HISTORY (?)}
3. Put the Date in the Parameter that pops up in the following format– MM/DD/YYYY
4. Save the Query and return the results to Excel
5. When you refresh the data, it will prompt you for the Parameter, and you can put in whatever you want for the date.