将列表结构作为参数传递给存储过程 [英] Pass a list-structure as an argument to a stored procedure

问题描述

是否可以将 Dictionary(或类似的东西；例如键/值对)作为参数传递给 MS SQL 2005 服务器上的存储过程?>

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

更新

这个问题让我问这个.

解决方案

您需要一种在 TSQL 中拆分和处理字符串的方法，有很多方法可以做到这一点.本文涵盖了几乎所有方法的优点和缺点:

"SQL Server 2005 及更高版本中的数组和列表，当表值参数执行时Not Cut it"作者:Erland Sommarskog

您需要创建一个拆分函数.拆分函数的使用方法如下:

SELECT*从你的表 y内部连接 ​​dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

我更喜欢使用数字表方法在 TSQL 中拆分字符串 但在 SQL Server 中有多种拆分字符串的方法，请参阅上一个链接，其中解释了每种方法的优点和缺点.

要使 Numbers Table 方法起作用，您需要进行一次时间表设置，这将创建一个表 Numbers，其中包含从 1 到 10,000 的行:

SELECT TOP 10000 IDENTITY(int,1,1) AS NumberINTO号码从 sys.objects s1交叉连接 sys.objects s2ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

设置 Numbers 表后，创建此拆分函数:

CREATE FUNCTION [dbo].[FN_ListToTableRows](@SplitOn char(1) --REQUIRED，用于拆分@List 字符串的字符,@List varchar(8000)--REQUIRED，要拆分的列表)退货表作为返回(------------------SINGLE QUERY-- -- 这将返回空行和行号----------------选择ROW_NUMBER() OVER(ORDER BY number) AS RowNumber,LTRIM(RTRIM(SUBSTRING(ListValue, number+1, CHARINDEX(@SplitOn, ListValue, number+1)-number - 1))) AS ListValue从 (SELECT @SplitOn + @List + @SplitOn AS ListValue) 作为内部查询INNER JOIN Numbers n ON n.Number 

您现在可以轻松地将 CSV 字符串拆分为表格并在其上加入.为了完成您的任务，如果您可以传入两个参数，其中一个键是值之一，然后像这样创建您的程序:

CREATE PROCEDURE StoredProcedureName(@Params1 整数,@Array1 varchar(8000),@Params2 整数,@Array2 varchar(8000))作为声明 @YourTable 表 (col1 int, col2 int)插入@YourTable(col1, col2)选择a1.ListValue, a2.ListValueFROM dbo.FN_ListToTableRows(',',@Array1) a1内部连接 ​​dbo.FN_ListToTableRows(',',@Array2) a2 ON a1.RowNumber=a2.RowNumber从@YourTable 中选择 *走

测试一下:

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

输出:

(4 行受影响)列 1 列 2----------- -----------127 162204 170110 163198 170(4 行受影响)

或者如果您想传入单个参数键值对，请使用以下内容:

CREATE PROCEDURE StoredProcedureName(@KeyValueList varchar(8000))作为声明 @YourTable 表 (RowKey varchar(500), RowValue varchar(500))插入@YourTable(行键，行值)选择LEFT(y.ListValue,CHARINDEX(',',y.ListValue)-1),RIGHT(y.ListValue,LEN(y.ListValue)-CHARINDEX(',',y.ListValue))从 dbo.FN_ListToTableRows(';',@KeyValueList) ySELECT * FROM @YourTable走

运行它:

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

输出:

RowKey RowValue------- ------------一个 5血脑屏障abc xyz(3 行受影响)

Is it possible to pass a Dictionary<String, String> (or something similar; like a key/value pair) as an argument to a stored procedure on an MS SQL 2005 server?

I'm looking for a practical example.

Update

This question lead me to ask this one.

解决方案

You need a way to split and process the string in TSQL, there are many ways to do this. This article covers the PROs and CONs of just about every method:

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

You need to create a split function. This is how a split function can be used:

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 but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 10,000:

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)

Once the Numbers table is set up, create this split function:

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 

You can now easily split a CSV string into a table and join on it. To accomplish your task, sif you can pass in two parameters, one of keys one of values, then create your procedure like this:

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

test it out:

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)

or if you want to pass in a single parameter key value pair use something like this:

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

run it:

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

OUTPUT:

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

(3 row(s) affected)

这篇关于将列表结构作为参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！