In this walkthrough, we'll see how to call a Stored Procedure directly from Excel.

We'll add a Stored Procedure to Microsoft's sample Northwind database, get Excel to call it, passing it a parameter, then we'll display its results in the Excel worksheet.
Here's what the stored procedure's results look like:

.. and this is how the users will see it in Excel (after manually adding a header row, and some formating on the figures):

Running a Stored Procedure from Excel 2007

First, let's create a simple Stored Procedure in the Northwind database, which takes a parameter (Customer ID) and returns some details of
orders that this customer has placed.

In SQL Server Management Studio if I run this Stored Procedure using the following command, it returns the results
that we saw earlier:

exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'

But, how do we get Excel to run this Stored Procedure ?

First, run Excel, and create a blank workbook.

Now, in cell B2, type in "Customer ID:", and in cell D2, type in "ALFKI" (both without the quotes). ALFKI is the customer code of one
of Northwind's example customers. We will get Excel to read in the value of cell D2 and pass it to the Stored Procedure as the CustomerID parameter.

Next, we need to add a Button to our spreadsheet. Click on the Developer tab, then the Insert button, then click on
the icon which looks like a button.

Now, hold down the left mouse button, and drag the outline of a button, somewhere around row 4 below our "Customer ID" label.
When you release the left mouse button, you'll see this dialog.

Click on the New button. You are then taken into Excel's beloved VBA scripting window.

Paste the following code between the Sub Button1_Click and End Sub lines.

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

This script uses the Microsoft ADO libraries to connect to SQL Server, so we need to tell Excel to include these libraries.
To do this, click on Tools then References.

Scroll down until you find the item "Microsoft ActiveX Data Objects 2.8 Libary" (or whichever version is most recent on your PC), tick the
checkbox next to it, then click on the OK button.

You can now click on File, then "Close and return to Microsoft Excel".

If all goes well, now, when you click on your button, Excel will run the Stored Procedure, and display the results in cell B8 onwards:

How cool is that !

You can now add formatting and a header row to the worksheet to make it look more professional. You could start by giving
"Button 1" a friendlier caption, by right-clicking on the button, select Edit text, and type in a friendlier caption.

When you Save this Excel file, remember to save it as a "Excel Macro-Enabled Workbook (*.xlsm)" file, as it contains Excel VBA script.

Closing thoughts

So, do I recommend that you throw away your Silverlight and WPF books, and start writing applications using Excel and Stored Procedures from now on ?

Of course not. But I have found this trick particularly useful with major applications which have been tested and deployed to live environments,
but then the user wants to see a little extra information from the database.

I don't really want to add extra screens to my application, then suffer having to go through the testing and deployment phase again.

Giving the users one-off reports, which they can run themselves like this, gives me a "quick win", without needing to make changes to my original app.

The other big advantage is that users love Excel (I'm sorry... but you know it's true).
And this gives us a really easy way to get raw data from SQL Server directly into Excel, ready for them to play with.