Using Stored Procedure as Data Source in Excel

When you want to use a SQL Server as external data source in excel, you can find, that it is not possible to select a stored procedure as a source of data in the Data Connection Wizard. This can lead to a conclusion, that it’s not possible to use a Stored Procedure as a source of data in Excel.

Excel Data Connection Wizard

Although, you cannot select a stored procedure, in the Data Connection Wizard, there is a simple workaround who to select a stored procedure as a source of data in Excel.

First, create a connection using the wizard and select any table as a source for the newly created connection. Once the connection is created, choose properties for the connection and switch to the Definition Tab.

Workbook Connections & Connection Properties

In the Definition type, change the Command Type to SQL and as Command text put the EXEC statement to execute the stored procedure. Once you enter, the Exec command, and confirm the dialog, you have fully configured  connection to SQL Server which source is a Stored Procedure.

Excel Connection Properties

On the dialog confirmation a warning message appears telling you, that the connection in Excel and the external connection files doesn’t match and that the link will be removed. Simply confirm the message by Yes as the connection will be stored in the excel workbook.

Confirmation of removing link to a connection file

Such modified connection you can use for importing data to Excel sheet, pivot table, pivot graph etc..