Pass Parameters To SQL Stored Procedure From Excel

[UncleLewis]

Hi all,

Using Office M365 and SSMS 12.0.2269.0
I created and tested a Stored Procedure in AdventureWorksDW2017 - works great

Now I am trying to pass a parameter to the Stored Procedure in Excel through Power Query

I created a Table named "param" and entered the parameter

I then opened the Advanced Editor in Power Query and entered this code

let
DepartmentName=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”DepartmentName”],
Source = Sql.Database("PCName\SQL2017INSTANCE2", "AdventureWorksDW2017",
[Query="exec usp_SelectEmployees '"
& DepartmentName
&"'"])
in
Source

But I am receiving an Expression.SyntaxError:Invalid identifier

Do you see anything that looks incorrect?

I've been staring at it for an hour now.

Thanks

[edhans]

I don't know how your stored procedure works, but the last thing you are passing is this:

"'"

 That single quote doesn't look right.

For a full overview of stored procedures and parameters from Power Query, see this article.

[UncleLewis]

Thanks,

Doesn't the ampersand concatenate the different strings together?

So really everything between {} one continuous string broken over multiple lines?

Thanks

[dax · Accepted solution]

Hi @UncleLewis , 

The & is used to connet the string, you could refer to @edhans 's suggestions to use ' ' ' in query. By the way, did you want to pass multiple parameters? If so, I think you could change your store prcedured like below(split_string is a split stored procedure)

create proc testp  @a varchar(20)  as
select *  from test1
where name in (select value from Split_String(@a, ','))

 Then change your M code like below(use " as Escape Characters )

let
    Source = Sql.Database("localhost", "newsql", [Query="exec testp '"&para&"'"])
in
    Source

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.