Call SQL Procedure in EXCEL

How to call a SQL Server Stored Procedure from EXCEL

Step 1:
Open the EXCEL work book and Click on "From Other Sources" and choose "From Microsoft Query".

A pop up will open to choose a Data Source.
Step 2:
If you are doing in first time, Select "New Data Source" then Click "OK".

Step 3:
A Data Source Name is our choice as per the naming convention.
Select a Driver as "SQL Server" from the Drop-down for point 2 in the Image.

Step 4:
Click on Connect (Point 3), and provide the details as mentioned below Image.

Once the credentials provided, click on "Options", so that we can choose the Database Name.

Now, click "OK".
Step 5:
Choose the Point 4 if you want all the columns. It's a optional Drop down. Then click OK.

Now, we can see the Data Source which we created. Select it and click "OK".

Step 6:
Choose Cancel, so that it will take us to SQL Query Window.

Click on "Yes", to close Microsoft Query Window.

Step 7:
Choose "Close" and click on SQL where we can write the SQL Queries.

I have created a procedure already with single Input Parameter.

Step 7:
//{CALL dbo.StoredProcedureName( Keep the Question marks each for one Input Parameter)}
{CALL MyOrg.dbo.GetAllEmpByDid (?)}
Then, click "OK".

Step 8:
Once click on "OK", It will asks for the Input.

We can find the procedure results in below Image. 

Close the "Microsoft Query" window.
Once it is closed, A " Import Data" wizard will be open and then Procedure results will be populated into Excel. It would be started from the cell which we selected.

Now, compare both the result sets from SQL Server and Excel( Just for unit testing).
















We can refresh the data and do the filters as per our requirements.

We also can see the connection properties and connection string settings here.

There are many features to filter and Import the data. I just explained how to call a procedure with parameter.