Today I want to explain how you can pass parameters to a SQL Server stored procedure. I myself was looking for a way to pass parameters to a SQL Server stored proc from Power Query. I spent a lot of time to search for a good article over the internet that explains how I could pass parameters to a stored proc from Power Query and show the results in Excel. But, I couldn’t find that much information around this as I expected. So, I decided to do some work around and you can read the results in this post. To simplify the solution, I’m going to use uspGetBillOfMaterials stored procedure in AdventureWorks 2012 database. The stored procedure accepts an integer number as ProductID and a date as CheckDate. So we need to pass two parameters to uspGetBillOfMaterials to get the results.
If we execute the stored proc in SSMS using
exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’
, we’ll get the following result:
Now, lets go to do some works on Power Query. So open Microsoft Excel and go to Power Query tab and select SQL Server database.
Now type Server, Database and SQL Statement, then click OK.
Continue reading “Power Query and SQL Server Stored Procedures”