Click here to Skip to main content
15,885,366 members
Articles / Programming Languages / SQL
Tip/Trick

Executing A Stored Procedure From Excel Sheet

Rate me:
Please Sign up or sign in to vote.
4.47/5 (15 votes)
3 Jan 2015CPOL3 min read 112.7K   20   7
How to execute a stored procedure from Excel Sheet

Introduction

Reports are one of the key requirements for management from any application whether it is a web application or Windows and they are interested to see it in Excel sheet. So, how about connecting an Excel sheet to a stored procedure and executing it directly from Excel sheet and get the result in the same sheet?

Yes, in this article, I am going to show you how to execute a stored procedure from Excel sheet. Let us consider a scenario where we have two tables in SQL Server database MyOrg, i.e., Department and Employee and say I want to execute a stored procedure GetAllEmpByDid as shown below:

Image 1

SQL
Create procedure [dbo].[GetAllEmpByDid]
(@Did as int)
as
select * from Employee where Did=@Did

Now, below are the steps that we need to perform to execute the stored procedure “GetAllEmpByDid” from Excel sheet.

Step 1

Open an Excel sheet, then go to:

DATA -> From Other Sources -> From Microsoft Query

Image 2

Step 2

Once you select From Microsoft Query option, it will fire a Choose Data Source popup. Now from Databases tab select <new> and click OK.

Image 3

Step 3

As you click OK, it will open another popup, i.e., Create New Data Source. Now give a name of data source, say “MTT” in textbox 1 and select driver as SQL Server from the dropdown list 2. Then click Connect button.

Image 4

Step 4

Once you click Connect, it will open one more popup SQL Server Login now give the SQL Server name in Server text box “thinkpad-pc\sqlexpress”, for valid server name it will enable Options button. Now click Options button.

Image 5

Step 5

Clicking on Options button will dropdown a form Options. Now select the database name from Databases dropdown list, say “MyOrg”. Finally click OK.

Image 6

Step 6

As soon as you click OK, it will close SQL Server Login. Now Click OK button of Create New Data Source popup, which will close Create New Data Source popup. Finally click OK button of Choose Data Source popup and it will close Choose Data Source popup and will open new popup, i.e., Query Wizard – Choose Columns.

Image 7

Step 7

Now click Cancel button of Query Wizard – Choose Columns which will prompt an alert Microsoft Query just click Yes button. Which will prompt another popup Add Tables. Now click on Close button of Add Tables, it will leave you on Microsoft Query Popup Window.

Image 8

Image 9

Step 8

From Microsoft Query Popup Window, click SQL button to open SQL popup window. Write the below query in SQL Statement box and press OK. It will prompt Microsoft Query alert once again and again press OK.

SQL
//{CaLL DatabaseName.dbo.StoredProcedureName(one question mark for each parameter)} 
{CALL MyOrg.dbo.GetAllEmpByDid (?)}

Image 10

Image 11

Step 9

After pressing OK of Microsoft Query alert, it will prompt a Parameter value window. Just give some default value say “1003”. And press OK and it will execute that stored procedure and give you the result set in Microsoft Query Window.

Image 12

Image 13

Step 10

Now close the Microsoft Query window. It will prompt another window, i.e., Import Data. Finally press OK to generate data on Excel sheet.

Image 14

Image 15

Step 11

Now we are ready with our Excel sheet executing stored procedure. If you want to execute once again, then go to DESIGN -> and click Refresh. It will prompt a Parameter value window once again, now give some other value say “1004”. And press OK to produce new result.

Image 16

Image 17

Step 12

Now save the Excel sheet and close it. Whenever you want to execute the procedure, just open the Excel sheet and refresh (Alt+F5) it from DESIGN tab.

Note

My special thanks to my friend Abhishek who helped me in creating this article.

Thanks for reading.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder ManzoorTheTrainer.com
India India
Manzoor is a Microsoft Certified Trainer who has been working on MS .Net technologies for more than a decade. Apart from development he is also passionate about delivering training on various MS .Net technologies and he has 10+ years of experience as a software development teacher. He writes articles for code-project as well. His YouTube channel has 1 million hits. He is the founder of ManzoorTheTrainer portal.

"I focus on simplifying, complex concepts..." - ManzoorTheTrainer

Founder of www.ManzoorTheTrainer.com [Free .net video tutorials on MS SQL Server, Asp.Net, C#.Net, Ado.Net, Entity Framework, MVC, Web Services, Android]

Comments and Discussions

 
PraiseExcelent Pin
marcelo iván rojas hernández21-Aug-18 8:19
marcelo iván rojas hernández21-Aug-18 8:19 
Very useful post.

Questionhave you consider to post this as a tip? Pin
Nelek7-Feb-15 4:52
protectorNelek7-Feb-15 4:52 
QuestionGood Pin
AbidHussain1286-Jan-15 22:09
AbidHussain1286-Jan-15 22:09 
QuestionNeat Pin
d_c_j6-Jan-15 4:19
d_c_j6-Jan-15 4:19 
AnswerRe: Neat Pin
Mohd Manzoor Ahmed6-Jan-15 20:47
professionalMohd Manzoor Ahmed6-Jan-15 20:47 
Questiongood Pin
PAULJ1236-Jan-15 4:08
PAULJ1236-Jan-15 4:08 
AnswerRe: good Pin
Mohd Manzoor Ahmed6-Jan-15 20:46
professionalMohd Manzoor Ahmed6-Jan-15 20:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.