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.

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

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

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

  • March 6, 2016 at 5:23 pm
    Permalink

    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.

    Reply
  • December 27, 2016 at 7:02 pm
    Permalink

    Thanks a lot for a great explanation , really i learned much , just small query . when retrieving data by query column data appeared and after returned to excel some of this data disappeared can you help me for that …. Thank you

    Reply
    • December 27, 2016 at 10:45 pm
      Permalink

      There is a row limitation in previous version of Excel. Could you please confirm the Excel version you are using?

      Reply
  • June 2, 2017 at 10:02 pm
    Permalink

    @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

    Reply
    • June 4, 2017 at 4:29 pm
      Permalink

      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.

      Reply
  • September 11, 2017 at 12:59 pm
    Permalink

    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?

    Reply
    • September 24, 2017 at 11:26 pm
      Permalink

      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.

      Reply
  • September 27, 2017 at 1:46 am
    Permalink

    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!

    Reply
    • October 26, 2017 at 12:25 pm
      Permalink

      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.

      Reply

Leave a Reply

%d bloggers like this: