Back
Excel

Running a SQL Stored Procedure from Excel (No VBA)

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

Today’s author is Mike Alexander, an Excel MVP who shows us how to run a Stored Procedure to get data from a SQL server. For more useful articles and videos, visit www.datapigtechnologies.com 

We all know we can use MS Query to get data from a SQL server. Typically though, we pull from a Table or a View. Well in some organizations, the IT department wants all interaction with the server to be done through Stored Procedure. This adds a level of risk management and makes the DBAs feel better.

So today, I’ll show you how to easily make Excel run a Stored Procedure to get data.

Step 1: Data tab – > From Other Sources -> From SQL Server  

image 

Step 2: Enter Credentials. Your server name can be an IP address  

image

Step 3: Choose any old table or view. Pick a small one because we’ll discard it later anyway.

image 

Step 4: Excel will pop up the Import Data dialog box. Click Properties here (NOT THE OK BUTTON).

  image

Step 5: Click on the Definition tab. There, change Command Type to SQL, and then enter your Stored Procedure name in the Command Text input.  

image

Step 6: Excel complains about something….blah…blah…blah. Click Yes – (as in yes I know what I’m doing).  

image

Step 7: Excel will activate the Import Data dialog box again. This time click OK to fire the Stored Procedure and return the results.

 image

Step 8: Marvel at your results

 image  

 Notes:

·        Excel will fire the Stored Procedure each time you “Refresh”

·        If you have to pass a parameter, you can enter it in the command text like this:

image



 ·        If you have to pass dynamic parameters you’ll have to turn to VBA. I’ll do a post on this later this week.

 ·        I assume you can do this with ORACLE databases too.

 ·        I’ve yet to test whether this will fire a Stored Procedure that doesn’t return data. In other words, Stored Procedures that perform Insert, Update or Delete actions. I assume that if you can, there is the possibility of updating SQL from Excel through a simple connection. Pretty cool. 

Top