将动态参数传递给 SQL Server 2008 中的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9923567/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing dynamic parameters to a stored procedure in SQL Server 2008
提问by themhz
I have this procedure that executes another procedure passed by a parameter and its parameters datefromand dateto.
我有这个过程，它执行另一个由参数及其参数datefrom和dateto.
CREATE procedure [dbo].[execute_proc]
@procs varchar(200),
@pdatefrom date,
@pdateto date
as
exec @procs @datefrom=@pdatefrom,@dateto=@pdateto
But I need to also pass the parameters dynamically without the need to edit them in the procedure. For example, what I am imagining is something like this
但我还需要动态传递参数，而无需在程序中编辑它们。例如，我想象的是这样的
CREATE procedure [dbo].[execute_proc]
@procs varchar(200),
@params varchar(max)
as
exec @procs @params
where @paramsis a string like @param1=1,@param2='somethingelse'
哪里@params是一个字符串@param1=1,@param2='somethingelse'
Is there a way to do this?
有没有办法做到这一点？
回答by Pondlife
It's not really clear what the point of your wrapper procedure is (auditing? debugging?), and it seems like a very awkward solution. If you explain why you want to do this, someone may have a completely different and hopefully better solution.
不太清楚你的包装程序的重点是什么（审计？调试？），这似乎是一个非常尴尬的解决方案。如果您解释了为什么要这样做，那么有人可能会有一个完全不同且希望更好的解决方案。
The biggest issue with your proposal is that you can only pass parameters as strings and that means you have to handle all the escaping, data conversion/formatting and SQL injection issues that come with dynamic SQL. It would be much better to call each procedure directly, passing correctly typed parameters from your calling code.
您的提案最大的问题是您只能将参数作为字符串传递，这意味着您必须处理动态 SQL带来的所有转义、数据转换/格式和 SQL 注入问题。直接调用每个过程会更好，从调用代码中传递正确类型的参数。
Having said all that, if you really want to do it then you can do something like this:
说了这么多，如果你真的想这样做，那么你可以这样做：
create proc dbo.ExecuteProcedure
@ProcedureName sysname,
@Parameters nvarchar(max),
@Debug bit = 0x0,
@Execute bit = 0x1
as
set nocount on
begin
declare @sql nvarchar(max)
set @sql = 'exec ' + quotename(@ProcedureName) + ' ' + @Parameters
if @Debug = 0x1 print @sql
if @Execute = 0x1 exec(@sql)
end
go
exec dbo.ExecuteProcedure 'dbo.SomeProc', '@p1 = 1, @p2 = ''themhz''s proc''', 0x1, 0x0
You should also have a look at sp_executesql, which does almost exactly what you want, but it needs to have all the parameter data types too, which you say is not possible in your scenario.
您还应该看看sp_executesql，它几乎完全符合您的要求，但它也需要具有所有参数数据类型，您说这在您的场景中是不可能的。
回答by James E Andreasen
- Put the stored procedure name in a varchar field in your client table
- Retrieve the SP name and assign it to a parameter ( spName) when the client is chosen.
In code create a function that returns a string
function PassStoredProcedureName(spName as string) as string
return spName
end function
Set your dataset to "Stored Procedure"
- Open a dataset Expression window
- Enter =Code.PassStoredProcedureName(Parameters!spName.value)
- 将存储过程名称放在客户端表的 varchar 字段中
- 检索 SP 名称并在选择客户端时将其分配给参数 (spName)。
在代码中创建一个返回字符串的函数
函数 PassStoredProcedureName(spName as string) as string
返回 spName
结束函数
将您的数据集设置为“存储过程”
- 打开数据集表达式窗口
- 输入 =Code.PassStoredProcedureName(Parameters!spName.value)
When you chose a client, the spName will be assigned to the parameter. When the dataset executes, it will pass the parameter to the function, which will pass the spName to the dataset.
当您选择客户端时，spName 将被分配给参数。当数据集执行时，它会将参数传递给函数，该函数会将 spName 传递给数据集。
I use this to execute custom stored procedures for clients when the same stored procedure will not work for all clients.
当相同的存储过程不适用于所有客户端时，我使用它来为客户端执行自定义存储过程。
Be sure to normalize the aliased field names so that data retrieval to a report does not break.
请务必规范化别名字段名称，以便对报告的数据检索不会中断。
Your stored procedures should always have the same parameter requirements even if they are not needed.
即使不需要，您的存储过程也应始终具有相同的参数要求。
