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...
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.

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.