将列表结构作为参数传递给存储过程

Question

是否可以将Dictionary<String, String>（或类似的键/值对）作为参数传递给MS SQL 2005服务器上的存储过程？

我正在寻找一个实际的例子。

更新

这个问题导致我问this one。

Answer 1

你需要一种方法来分割和处理TSQL中的字符串，有很多方法可以做到这一点。本文介绍了几乎每一个方法的优点和缺点：

"Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

你需要创建一个分裂的功能。这是一个分裂的功能如何使用：

SELECT 
    * 
    FROM YourTable        y 
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value 

I prefer the number table approach to split a string in TSQL但也有许多方法来拆分在SQL Server中的字符串，见前面的链接，这说明各的优点和缺点。

对于数字表的方法来工作，你需要做的这一次表的设置，这将创建一个包含从1到10000行的表Numbers：

SELECT TOP 10000 IDENTITY(int,1,1) AS Number 
    INTO Numbers 
    FROM sys.objects s1 
    CROSS JOIN sys.objects s2 
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number) 

一旦Numbers表格设置，创建此分割功能：

CREATE FUNCTION [dbo].[FN_ListToTableRows] 
(
    @SplitOn char(1)  --REQUIRED, the character to split the @List string on 
    ,@List  varchar(8000)--REQUIRED, the list to split apart 
) 
RETURNS TABLE 
AS 
RETURN 
(
    ---------------- 
    --SINGLE QUERY-- --this will return empty rows, and row numbers 
    ---------------- 
    SELECT 
     ROW_NUMBER() OVER(ORDER BY number) AS RowNumber 
      ,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue 
     FROM (
       SELECT @SplitOn + @List + @SplitOn AS ListValue 
      ) AS InnerQuery 
      INNER JOIN Numbers n ON n.Number < LEN(InnerQuery.ListValue) 
     WHERE SUBSTRING(ListValue, number, 1) = @SplitOn 
); 
GO 

您现在可以轻松地将CSV字符串拆分为表格并加入其中。为了完成你的任务，SIF可以在两个参数，键的值之一的一个传球，然后创建一个这样的过程：

CREATE PROCEDURE StoredProcedureName 
(
    @Params1 int 
    ,@Array1 varchar(8000) 
    ,@Params2 int 
    ,@Array2 varchar(8000) 
) 
AS 

DECLARE @YourTable table (col1 int, col2 int) 

INSERT INTO @YourTable 
     (col1, col2) 
    SELECT 
     a1.ListValue, a2.ListValue 
     FROM dbo.FN_ListToTableRows(',',@Array1)   a1 
      INNER JOIN dbo.FN_ListToTableRows(',',@Array2) a2 ON a1.RowNumber=a2.RowNumber 

select * from @YourTable 

GO 

测试出来：

exec StoredProcedureName 17,'127,204,110,198',7,'162,170,163,170' 

OUTPUT：

(4 row(s) affected) 
col1  col2 
----------- ----------- 
127   162 
204   170 
110   163 
198   170 

(4 row(s) affected) 

，或者如果你想在一个单一的参数键值对使用这样的事情经过：

CREATE PROCEDURE StoredProcedureName 
(
    @KeyValueList varchar(8000) 
) 
AS 

DECLARE @YourTable table (RowKey varchar(500), RowValue varchar(500)) 

INSERT INTO @YourTable 
     (RowKey, RowValue) 
    SELECT 
     LEFT(y.ListValue,CHARINDEX(',',y.ListValue)-1),RIGHT(y.ListValue,LEN(y.ListValue)-CHARINDEX(',',y.ListValue)) 
     FROM dbo.FN_ListToTableRows(';',@KeyValueList) y 

SELECT * FROM @YourTable 

GO 

运行：

exec StoredProcedureName 'a,5;b,BBB;abc,xyz' 

OUTPUT：

RowKey RowValue 
------- ----------- 
a  5 
b  BBB 
abc  xyz 

(3 row(s) affected) 

Answer 2

格式此解释为XML，并在通过这个到SQL Server。它理解XML数据类型并可以查询它。

<Data> 
    <Entry Key="1" Value="One" /> 
    <Entry Key="2" Value="Two" /> 
</Data> 

并将其作为SqlDataType.Xml传递。 SQL 2005和更高版本能够从XML进行查询。有很多关于如何在SQL Server中使用XML的例子。