如何将可变数量的参数传递给SQL Server存储过程?
问题描述:
我为我的小型Web应用程序使用了SQL Server 2005。我希望将参数传递给SP。 但有一个条件。可随时更改的参数数量。 想到,这一次我通过名称和地址,下一次我通过姓名,地址,如何将可变数量的参数传递给SQL Server存储过程?
这个参数范围可能是1-30,
答
您可以在SP上的参数设置的默认值级别,以便参数变为可选。
例如
CREATE PROCEDURE dbo.Test
@param1 int, -- Not an optional
@param2 int = 10, --Optional
@param3 bit = 0, --Optional
@param4 varchar(50) = '', --Optional
@param5 nvarchar(100) = null --Optional
-- more parameters .....
AS
BEGIN
-- The SQL goes here...
END
然后,您可以根据您的选择填充可选参数。
答
你声明使用默认参数的程序,你命名参数而不是位置参数来调用它:
CREATE PROCEDURE usp_myProcedure
@name varchar(100) = '',
@surname varchar(100) = '',
@address varchar(100) = ''
AS
BEGIN
...
END
从T-SQL调用它:
exec usp_myProcedure @name='John', @surname = 'Doe';
exec usp_myProcedure @name='Jane', @address = '123 Anystreet';
从C#调用它:
SqlCommand cmd = new SqlCommand('usp_MyProcedure', ...);
cmd.CommandType = commandtype.StoredProcedure;
cmd.Parameters.AddWithValue('@name', 'John');
cmd.Parameters.AddWithValue('@surname', 'Doe');
答
您可以使用XML类型。下面是一个示例过程:
CREATE PROCEDURE udsp_VariableParams(@params XML) AS BEGIN DECLARE @vdoc INT EXEC sp_xml_preparedocument @vdoc OUTPUT, @params SELECT * FROM OPENXML(@vdoc, '/params/param', 1) WITH ([paramname] VARCHAR(40), [paramvalue] VARCHAR(150)) EXEC sp_xml_removedocument @vdoc END EXEC udsp_VariableParams '<params> <param paramname="name" paramvalue="value"/> <param paramname="address" paramvalue="value"/> </params>' EXEC udsp_VariableParams '<params> <param paramname="name" paramvalue="value"/> <param paramname="surname" paramvalue="value"/> <param paramname="address" paramvalue="value"/> </params>'
答
从XML准备和提取标记是一个性能杀手。不一致的执行时间取决于计划是否被缓存。
改为使用NVARCHAR(MAX)是更好的选择。只需使用“@ name = Value”对准备参数字符串,并根据需要添加唯一的参数分隔符。在程序内部使用SUBSTRING,CHARINDEX等来获取单个参数。
我认为这对OPs问题有点矫枉过正 - 但认为它对于我目前遇到的问题可能非常有用! :) 谢谢! +1 – 2012-03-22 09:11:28