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.
Select a name for the query, I names it GetBOM. Then from Home tab click on “Close & Load”.
So far we’ve loaded the results of to Excel. Now we need to pass the parameters to the stored proc. As you can see, the above stored proc accepts an integer and a date as parameters. So we create a table in the Excel sheet with two columns that contain the parameters. Name the table as “param”.
To make out life easier I changed the format cell of the “Check Date” column to Text, other wise we’ll need to convert it in Power Query. We still need to convert ProductID in Power Query.
Now go back to Power Query, right click on GetBOM and click Edit
In GetBOM Query Editor window, go to View tab and click “Advanced Editor”.
Here we need to add some codes. The scripts in Power Query are written in a language called “M”.
All we need is to parameterise the query so that we read the contents of from the “param” table we defined before. In M language, to read a cell content we need to address the table as below:
In the above code, TABLE_NAME is “param” in our sample, ROW_NUMBER is the number of row that we need to load its content and COLUMN_NAME is the name of the column. So to adders the value of the first column of “param” table, the above code will be as below:
and for the second one it will be like this:
Now we need to replace the constants from the query with the expressions above to make the query parameterised. You can copy the code below in the Advanced Editor:
Source = Sql.Database(“SQL_SERVER_INSTANCE NAME“, “AdventureWorks2012”,
[Query=”exec [dbo].[uspGetBillOfMaterials] ‘”
& “‘, ‘”
You need to put your own SQL Server instance name in the above code.
Note to the single quotation marks in the code.
To concatenate texts we use “&” in M language. Click Done then click Close and Load from Home tab.
Now if you change the values of the “param” table and refresh data you’ll see the new results in Excel.
For instance, change the ProductID from 727 to 800 then refresh data. You’ll see the below screen:
As you can see the first parameter to pass to stored procedure is changed to 800. Click RUN to see the results in Excel.
We are done!
10 thoughts on “Power Query and SQL Server Stored Procedures”
You are brilliant! I really enjoy reading your blog. Can you share some insight into cleaning/preparing data from ERP/OLTP db and load it into fact tables efficiently?
thank you for your post. It’s really nice
My question would be if you can do the opposite. Create a stored procedure, that then are using DAX to create a Excel file?
Thank you for such an insightful post. I’m loving your blog and have been using a lot of your posts.
I’ve been trying to replicate your results in a native database query and not a stored procedure, but i’m not being successful at it.
Basically i want to be able to do:
BeginDt = GetValue(rng_BeginDt),
EndDt = GetValue(rng_EndDt),
Source = Sql.Database(“XXX”, “YYY”, [Query=”
SalesDt BETWEEN ‘BeginDt’ AND ‘&EndDt&’
GetValue is a function that i’ve defined to grab the value from the range name.
I’ve tried different variations, like using &’s or also doing your method of calling the date without a function using the Excel.CurrentWorkbook method.
I get the following error:
DataSource.Error: Microsoft SQL: Conversion failed when converting datetime from character string.
Message=Conversion failed when converting datetime from character string.
Any ideas or guidance would be extremely appreciated.
You wrote: To make out life easier I changed the format cell of the “Check Date” column to Text, otherwise we’ll need to convert it in Power Query
I would like to pass to sql query the number that represents the date.
In your example Check Date 2009-01-01 is 39814 in Excel and I would like to pass 39814 to my sql query.
Could you please show required conversions in Power Query
Just helped me a lot! Thank You!!!
Seems when we change the ProductID is returning more than the parameters. Even in your screen shot shows more Products than just than those with Product ID 800 specified in your parameters. Any thoughts?
Welcome to BIInsight.com and thanks for your feedback.
Please note that the column shown in the screenshot is NOT ProductID, it is ProductAssemblyID which is a totally different ID.
The ProductID though is used in the filter.
Hopefully that helps.
Very worthwhile content! Thank You
Does anyone know where I can find out how to pass multiple dates like:
where Dates in (date1, date2, date3)
In parameters? I can get it to work fine with:
where Dates = ‘date1’
The challenge for me is referencing multiple parameters while ensuring the integrity of the SQL remains in tact. (& Date1, &Date2, &Date3) does not work.