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.