Sql Server 2005中的动态存储过程

Is it possible to write a stored procedure with dynamic parameters in sql server 2005

是否可以在sql server 2005中编写带有动态参数的存储过程

3 个解决方案

#1

Technically no, but there's a workaround: temp tables. Create a temp table, then set up your stored procedure to read its configuration from the temp table.

技术上没有,但有一个解决方法:临时表。创建临时表,然后设置存储过程以从临时表中读取其配置。

Here's an example of how to call it:

以下是如何调用它的示例:

CREATE TABLE #inputParams (ParamName VARCHAR(20), ParamValue VARCHAR(20))
INSERT INTO #inputParams (ParamName, ParamValue) VALUES ('First Name', 'John')
INSERT INTO #inputParams (ParamName, ParamValue) VALUES ('Last Name', 'Doe')
EXEC dbo.USP_MyStoredProcedure

and then inside your stored procedure, you could retrieve whatever parameters you needed by checking for the existence of that temp table, and querying the parameters from it like this:

然后在存储过程中,您可以通过检查该临时表的存在来检索所需的任何参数,并从中查询参数,如下所示:

CREATE PROCEDURE dbo.USP_MyStoredProcedure AS
DECLARE @FirstName VARCHAR(20)
SET @FirstName = SELECT ParamValue FROM #inputParams WHERE ParamName = 'First Name'
GO

That way, your stored procedure's number of parameters never changes, which makes it easier for some dev applications to call it over time, but this is reeeeeally dangerous. From the outside of the stored proc, you never have any idea what version of the stored proc you're calling, and whether or not it's going to do anything with the parameters you're sending in.

这样,您的存储过程的参数数量永远不会更改,这使得某些开发应用程序可以更容易地调用它,但这是非常危险的。从存储过程的外部,您永远不知道您正在调用的存储过程的版本,以及它是否将对您发送的参数执行任何操作。

What you might think about instead is abstracting away your stored proc inside another stored proc as a wrapper.

您可能会想到的是将存储过程作为包装器抽象出另一个存储过程。

#2

No, both the count and parameter types are fixed as part of the stored procedure declaration.

不,计数和参数类型都是固定的,作为存储过程声明的一部分。

#3

If there's a discrete set of parameters you could pass them into the sproc as XML. Then you can shred the XML into variables inside the body of the sproc e.g.

如果有一组离散参数,您可以将它们作为XML传递给sproc。然后你可以将XML分解为sproc体内的变量,例如:

CREATE PROCEDURE MyDynamicStoredProc  
@ParameterXML XML
AS

SET NOCOUNT ON

DECLARE @Parameter1 NVARCHAR(50)
DECLARE @Parameter2 INT

SET @Parameter1 = @ParameterXML.value('/Root/Parameters/StringParameter/@Value','nvarchar(50)')
SET @Parameter2 = @ParameterXML.value('/Root/Parameters/IntegerParameter/@Value','int')
...

NB: My XQuery-fu is weak, don't rely on this example!

NB:我的XQuery-fu很弱,不要依赖这个例子!

#1