Running a SQL Stored Procedure with Parameter in Excel (no VBA)

There are many methods you can get the data from SQL server database to Excel. One common method is going to Data >>  From Other Sources menu option to fetch the data from SQL server tables or views to Excel work sheet. Getting data from table or view is relatively simple and straight forward but getting data using stored procedure is quite a difficult task. In addition to that if you want to fetch data by passing the parameter values to the stored procedure, that makes it more tougher. In a real world we will not get all the data from table or view directly, the data which are in table needs some processing and manipulation etc. before getting into excel. For example if  we want to get the sales data from a database we may require to get the data from multiple table, then do some calculation like discount, tax etc. before bringing them to excel. In this scenario, stored procedures are best suited. Also if we are using the table or view we are forced to bring all the data from the database to excel and then do the filter as we want at the excel sheet.(this will slow down the data fetching), but if we use the stored procedure we can pass for example ‘from date’ and ‘to date’ to get the data for that period only.

Please watch the below video to find out how you can create a stored procedure in SQL server and then get those data to excel for the date passed.

 


   3 Comments


  1. wilma
      December 2, 2016

    I love this, this is fantastic but I am getting the following error and have no idea how to fix this
    Parameters are not allowed in queries that can’t be displayed graphically?

    Please could you help

    • Wilson
        February 16, 2017

      Hi Wilma,
      You just need to hit the ‘YES’ button at the video 8:47 position.
      I suggest you to watch the video from 7 minutes on wards one more time to see that error popping up at 8.47 and how we are managing it.
      Let me know if you need further assistance.

  2. Vi
      February 24, 2017

    Great. I follow your guide to do for my SP. I got error ‘Invalid character value for cast specification’ when call the SP on Excel Query, although I can get correct result when executing my SP on SQL. I have 2 parameters on my SP: one is String and one is Int. can you advise me?

Leave a Reply

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