Browse > Home / Business Intelligence, Featured, Technology / Using Excel to run MS SQL Stored Procs

Using Excel to run MS SQL Stored Procs

April 13, 2010

Today I had an opportunity to install a spreadsheet for a customer.  It may seem simple…a spreadsheet…but the power it gave to it’s user was unimaginable.

I’ve been working in IT since 1993.  In that time, I’ve become the Excel champion.  Excel is cheap (relatively), installed nearly everywhere, and most everyone knows how to use it.  IT rejects Excel…because it’s cheap and easy.  We technicians like things to be complicated.  It makes us feel smart when we can deploy an app that most people would never understand.  But year after year, I find that the cheap, simple Excel spreadsheet is the one thing that can always get you a “thank you” and “you’ve made my job so easy” response from a customer.

With today’s install, I was able to get my smart guys to even look at Excel as something complex.  Using a stored procedure in MS SQL 2005 and the data connection in MS Excel, we were able to create an Excel Macro that allows a user to click on a menu item and execute that stored procedure using parameters in the Excel spreadsheet.

After creating the connection, the next step was to establish parameters/prompts in the spreadsheet.

Now that the parameters were established, the user could bring back the data into a table in Excel to view the results.  We chose to hide that tab and instead allow the users to manipulate with a simple but elegant Pivot Table (this did require some macro work).

THERE!  DONE!

Now, with some additional time, we could clean up the macro, create some error handling and actually move the running of the stored proc to the macro…with…some…additional…time. The solution is clean and simple and the user is happy.  For prototyping and user solutions that are going to just a few people, sometimes easier and faster is better.

- Jodie

Written by Jodie Heflin · Filed Under Business Intelligence, Featured, Technology 

Comments

2 Responses to “Using Excel to run MS SQL Stored Procs”

  1. LUCRUM Incorporated on April 13th, 2010 5:58 pm

    Using Excel to run a Stored Proc: Using Excel to run MS SQL Stored Procs http://tinyurl.com/yedvlzd

  2. tomtheideaguy on July 15th, 2010 6:42 pm

    RT @LUCRUMinc: Using Excel to run MS SQL Stored Procs http://bit.ly/aas1gM

Got something to say?