Forum Discussion

UncleLewis's avatar
UncleLewis
Responsive Resident
6 years ago
Solved

Pass Parameters To SQL Stored Procedure From Excel

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

Need Help
  • dax's avatar
    dax
    6 years ago

    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.

3 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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's avatar
      UncleLewis
      Responsive Resident
      6 years ago

      Thanks,

       

      Doesn't the ampersand concatenate the different strings together?

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

       

      Thanks

      • dax's avatar
        dax
        Community Support
        6 years ago

        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.