Skip to main content
Skip to main content
Microsoft 365

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.

Excel icon

Microsoft Excel

Turn data into insights.

Get Excel

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. 

 

Related posts

Image for: View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365
• 5 min read

View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more.

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards
• 5 min read

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting
• 6 min read

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…