Running a SQL Stored Procedure from Excel (No VBA)

https://blogs.office.com/2010/06/07/running-a-sql-stored-procedure-from-excel-no-vba/

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 

 

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

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

 

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

  

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. 

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

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

 

Step 8: Marvel at your results

   

 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:

         

  • 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. 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s