将动态参数传递给SQL Server 2008中的存储过程

I have this procedure that executes another procedure passed by a parameter and its parameters datefrom and 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 @params is a string like @param1=1,@param2='somethingelse'

@params是一个字符串，比如@param1=1，@param2='somethingelse'

Is there a way to do this?

有办法吗?

2 个解决方案

#1

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，它几乎完全符合您的要求，但它也需要拥有所有参数数据类型，您说在您的场景中不可能实现这一点。

#2

Put the stored procedure name in a varchar field in your client table
将存储过程名放在客户表的varchar字段中
Retrieve the SP name and assign it to a parameter ( spName) when the client is chosen.
检索SP名称，并在选择客户端时将其分配给一个参数(spName)。
In code create a function that returns a string

在代码中创建一个返回字符串的函数

function PassStoredProcedureName(spName as string) as string

函数passstoredprogramurename (spName as string)作为string

return spName

返回spName

end function

结束函数
Set your dataset to "Stored Procedure"

将数据集设置为“存储过程”
Open a dataset Expression window
打开数据集表达式窗口
Enter =Code.PassStoredProcedureName(Parameters!spName.value)
输入= Code.PassStoredProcedureName(参数! 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.

您的存储过程应该始终具有相同的参数需求，即使不需要它们。

#1