Introduction

A couple of week ago, I read this post by David Poole about reading data from SQL Server into Excel, where the data source was a parameterised stored procedure.  David concludes that a small amount of (albeit trivial) VBA code is required in order to make this work.  However, deep down in my psyche, I knew I’d been able to do this without writing any VBA code.  So, after a bit of playing around, I’ve managed to solve the problem.

For the purposes of this document, I’ve setup a really simple database for us to use, using the following script.

CREATE DATABASE xlsproc
GO
USE xlsproc
GO
CREATE TABLE [TestTable](
    [ID] INT NOT NULL IDENTITY(1,1),
    [Category] VARCHAR(30) NOT NULL,
    [Product] VARCHAR(30) NOT NULL,
    [ListPrice] INT NOT NULL,
    PRIMARY KEY ([ID])
)
GO
INSERT INTO \[TestTable\]\(\[Category\],\[Product\],\[ListPrice\]\)
VALUES ('Cat A', 'Product A1', 234)
INSERT INTO \[TestTable\]\(\[Category\],\[Product\],\[ListPrice\]\)
VALUES ('Cat A', 'Product A2', 337)
INSERT INTO \[TestTable\]\(\[Category\],\[Product\],\[ListPrice\]\)
VALUES ('Cat B', 'Product B1', 653)
INSERT INTO \[TestTable\]\(\[Category\],\[Product\],\[ListPrice\]\)
VALUES ('Cat B', 'Product B2', 132)
GO
CREATE INDEX [idx_TestTable_Category] on \[TestTable\]\(\[Category\]\)
GO
CREATE PROCEDURE [dbo].[GetProductsForCategory]
    @Category VARCHAR(30)
AS
    SELECT [ID], [Product], [ListPrice]
    FROM [TestTable]
    WHERE [Category] = @Category
GO

Our objective is to allow users to enter a value into a cell in Excel, and have this value passed to the GetProductsForCategory stored procedure, and the results returned to Excel.

Querying with Excel 2007

I’m using Excel 2007, and the process has been made significantly easier since Excel 2003.  To start, we’ll set up a cell on our spreadsheet which will contain our parameter.  Something like this should suffice:

To get data into our spreadsheet, we choose “Get External Data –>  From Other Sources –> From Microsoft Query” from the Data tab on the ribbon:

We don’t use the From SQL Server option, as this will not allow us to write queries with parameters later on.  From the next dialog box, choose and setup the connection appropriately, as per the next series of images.  In particular, note that in the SQL Server Login box, we extend the options and choose our xlsproc database.

Now, we have our data source setup, so we select it and for now, we add the columns from TestTable to our query results:

Click Next through the rest of the wizard pages and choose finish.  Choose to put the result set in cell A3.  This gives us a worksheet looking like the following:

Now, from the Data ribbon, choose “Connections” and then click the “Properties…” button.  On the “Definition” tab of the resulting dialog box, edit our command text to read “exec dbo.GetProductsForCategory ?”.  Note the question mark.  This tells Excel that we will supply a parameter to be used here.

Excel will kindly ask us to provide details of the parameter:

Note that we choose the cell we’ve already setup to contain the parameter, and check both of the options in order that whenever we change the value in cell B1, the dataset will update itself.

Querying with Excel 2003

The process for Excel 2003 is similar.  As there is no ribbon, the command to start getting data is from the menu item “Data –> Get External Data –> New Database Query”.  We then specify our data source as with 2007.

However, on the last step of the wizard, rather than pressing finish, we choose to edit or view our query in Microsoft Query.  At this stage, we then press the SQL button, and type in a sample query using the stored procedure. Unlike in 2007, we can’t just throw a question mark in at this point:

After clicking OK, you now need to choose “File –> Return data to Microsoft Excel”. Now, unfortunately, I no longer have a copy of Excel 2003 installed, so from memory, once the data has been returned to Excel, from the Tools –> Macro menu, choose Microsoft Script Editor.

Then, on the script search for the command you entered above (i.e. the “exec dbo.GetProductsForCategory ‘Cat A’” string).  Replace the “’Cat A’” with a ?.  Note that we don’t put single quotes around the exclamation mark.  Now, save this.  When you go back to the Excel worksheet, the program will prompt you for the parameter, just like in Excel 2007.