Running a SQL Stored Procedure from Excel with Dynamic Parameters

Here is the interesting article about how to generate report from stored procedure to Excel sheet.

This is called as Pivot reports.

Here we can also call the stored procedures with input parameters.

Below are the step by step process to do the same :

1. Open Excel ( Start -> Excel )

2. In Tabs Click on Data -> Connections


















3. In Workbook Connections wizard click on Add and select your database server/ Instance name and Add that to workbook and then close the wizard.

<< So far we are done to make the connection with the DB server >>

4. After this again in the Data tab From other sources and select From microsoft query.

















5. In choose data sources wizard select the appropriate database name and click on OK.

















6. This will open up a Query wizard to select the parameters.

7. Here select any of the dummy parameters from the existing values ( we dont use this in future but select a dummy values ) and then click on Next button

















8. Query Wizard Filter data -> click Next

9. Query Wizard Sort Order -> click Next

10. Query Wizard Finish -> select "View data or edit query in microsoft query" -> click on Finish

















11. In Microsoft query window click on SQL button on Top as show in the below screen shot.

















12. In the SQL Statement window place your Stored Procedure with dummy parameter what ever you want ( This we can configure it later as dyncamic )

















13. Then click on OK. This will prompt you with warning message saying "SQL query cannt be represented graphically" ignore this warning continue anyway and close the wizard window.

14. Then it will open Import data wizard.

















15. Click on Properties -> in connection properties windows select Definitions tab.

16. Here in CommandText section you will find your query with the dummy parameter. Here replace the parameter with ?

















17. Click OK and close the wizard window.

18. After this excel sheet will prompt you for the input. Provide the input and click on OK, Here is the result

















NOTE :

1. Inputs can be configured from the Excel sheet cell also.

2. Reports can be converted in to graphics also

3. Reports can be configured as auto refresh on periodic basis.



Thank you !!!