参数化 SQL 存储过程 Excel Get&Transform [英] Parameterise SQL Stored Procedure Excel Get&Transform
问题描述
我正在尝试将 SQL Server 中的存储选择过程调用到 Excel 中.
I'm trying to call a stored select procedure from SQL Server into Excel.
在 SSMS 中可以写成
In SSMS could be written as
@val1=1;
@val2=2;
Execute SP_DoStuff(@param1=@val1,@param2=@val2);
看类似的问题,一个常见的答案是连接参数并作为动态SQL运行.例如:使用单元格中的值作为参数在电源查询编辑器的存储过程中
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
答案在那里
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
除非我在这里遗漏了一些东西,否则由于 YourDateMethod 的字符串连接,这个已建立的方法看起来很容易注入.
Unless I'm missing something here, this established method looks wide open to injection due to string concatenation of YourDateMethod.
有没有一种方法可以安全地将 Excel 中的值输入到参数中?
Is there a method to feed values from Excel into the parameters safely?
例如 odbc 允许准备令牌
For example odbc allows preparing with tokens
Execute SP_DoStuff(@param1=?,@param2=?);
然后喂?作为运行时的值
Then feed in ? as values at runtime
推荐答案
您可以使用 Value.NativeQuery 编写参数化查询.例如,如果您想要一个只显示名称为 Alice 的列的查询,您可以执行
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" ])
这篇关于参数化 SQL 存储过程 Excel Get&Transform的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!