How to execute stored procedure in excel with parameters

Microsoft Excel is a very powerful tool, using excel we can connect to all most all known data sources available and create a report easily. In this post, we will learn how to connect to a SQL Server database from excel and execute stored procedure in excel with parameter values passed run time.

Lets create a sample stored procedure first, once created follow the steps to configure the same in excel.


CREATE PROC usp_ExportExcelSample
(@name VARCHAR(100) = ''
,@type VARCHAR(20) = ''
)
AS
BEGIN
SELECT * FROM sys.objects a
WHERE a.is_ms_shipped = 1
AND a.name = COALESCE(NULLIF(@name, ''), a.name)
AND a.type_desc = COALESCE(NULLIF(@type, ''), a.type_desc)
END

Step 01: RUN → EXCEL

run-excel-01

Step 02: Choose blank workbook

blank-worksheet-02

Step 03:

connect-microsoft-query-03

Step 04:

create-data-source-04

If you have already a connection created earlier then you can use the same otherwise you can create a new one.

Step 05:

create-data-source-05

Step 06:

create-data-source-06

Step 07:

create-data-source-07

A new connection is created with name SQL CONNECTION

Step 08:

select-data-source-08

Step 09:

excel-query-wizard-09

Step 10:

excel-query-wizard-10

Step 11:

excel-query-wizard-11

Step 12:

excel-query-wizard-12

Step 13:

excel-query-wizard-13

 

Pass some values to the parameters here for the first execution, don’t worry we will make them dynamic later.

Step 14:

excel-query-wizard-14

Step 15:

excel-import-data-15

I am leaving 2 rows above and loading data from 3rd row, that is just for my convenient because I am going to use those two rows as a placeholder for my stored procedure parameters.

Step 16:

excel-import-data-16

Step 17:

sql-parameter-set-17

Step 18:

sql-parameter-set-18

Step 19:

sql-parameter-set-19

Step 20:

sql-parameter-set-20

Step 21:

sql-parameter-set-21

Step 22:

sql-parameter-set-22

Step 23:

sql-parameter-set-23

Step 24:

sql-execute-24

11 thoughts on “How to execute stored procedure in excel with parameters”

  1. Congratulations on posting! Excellent tutorial on how to connect Excel to SQL Server. This material was very useful for me. Now I can create some dasboards (simple) in Excel, with dynamic content.
    Thank you for sharing your knowledge.
    Brazilian greetings.

  2. @Prashad Sahoo and those who know

    Thank you for this enlightening article.

    Two Questions
    1. How do I make date parameters be recognized as dates by SQL Server 2008. I get message “incorrect syntax near @P1” if i make the query pick the date from a cell in the spreadsheet.

    2. is there a way of telling the user what Parameter e.g. Start Date and End Date in my instance rather than Parameter1 and Parameter2?

    Thank you for your response

    1. 1. You need to make sure the parameter sequences are right while calling the proc. Lets say you have a proc named usp_GetEmp and it has 3 parameters @EmpID INT, @FromDate DATE, @ToDate DATE in a sequence then you will have to call the proc in excel something like exec usp_GetEmp ?, ?, ? (first question mark for @EmpID, second one for @FromDate and third one for @ToDate. Check the Step 18 image. If the parameter is date then automatically the input text from the cell will be implicitly converted to date, all you need to make sure the date type is correct.
      2. Check the last image step#24, we are binding one parameter input to one particular cell in the excel, so you can write about the parameter in the next cell so user knows what to input in the cell.

      Hope that answers your question.

  3. Hey Prasad,
    Thanks for this beautiful tutorial.
    It has helped me a lot.
    Just one more ask,
    If i want to reconnect to this excel worksheet what are the steps to be followed?
    As in I successfully created a excel worksheet which fetches query result based on some parameters.
    But then I closed the excel and when I reopened the Excel, I was unable to reproduce the same worksheet.
    Do i need to follow the whole procedure again?

    1. Glad you liked it.
      No you do not have to do this again. The excel file stores the connection and the setup. You just need to click on “Refresh” button to get the stored proc executed and fill the data.

  4. This is great. The only obstacle I’m running into is making this work on other workstations. It is tied to my computer, even though it should be using the trusted connection. Is there a workaround or do I need to set this up on each individuals’ computer separately? Thank you!

    1. Are you using windows authentication or database authentication? In case of windows you will have to add those logins with READ ONLY rights. If you have a sql user then that should work from any workstations. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
2 × 26 =