Parameterise SQL Stored Procedure Excel Get&Transf

Question

I'm trying to call a stored select procedure from SQL Server into Excel.

In SSMS could be written as

@val1=1;
@val2=2;
Execute SP_DoStuff(@param1=@val1,@param2=@val2);

Looking at similar questions, a common answer is to concatenate the parameter and run as dynamic SQL. For example: Use values in cells as parameters in stored procedure in power query editor

The answer there is

Source = Sql.Database("xxxxxxxxxxxxxxxxx", "xxxxxxxxxxx", [Query=
"#(lf)#(lf)EXEC#(tab) [dbo].[TestParameter]#(lf)
#(tab)#(tab)@from = '" & YourDateMethod & "'#(lf)
#(tab)#(tab)@isValid = 1,#(lf)
#(tab)#(tab)@someInt = 90"
]) 
in Source 

Unless I'm missing something here, this established method looks wide open to injection due to string concatenation of YourDateMethod.

Is there a method to feed values from Excel into the parameters safely?

For example odbc allows preparing with tokens

Execute SP_DoStuff(@param1=?,@param2=?);

Then feed in ? as values at runtime

Answer 1

You can use Value.NativeQuery to write parameterized queries. For example, if you wanted a query that only showed columns with the name Alice, you could do

Value.NativeQuery(Sql.Database("server", "database"), "select Name from Employees where Name = @myName", [ myName = "Alice" ])