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.
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.
Help people and teams do their best work with the apps and experiences they rely on every day to connect, collaborate, and get work done from anywhere.
