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:
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
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.
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.
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.
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.
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.
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.
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.
//{CaLL DatabaseName.dbo.StoredProcedureName(one question mark for each parameter)}
{CALL MyOrg.dbo.GetAllEmpByDid (?)}
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.
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.
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.
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.
