逗号分隔值插入在SQL Server 2005中

How can I insert values from a comma-separated input parameter with a stored procedure?
For example:

如何从逗号分隔的输入参数中插入值与存储过程？例如：

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

you can see that I have two comma-separated value lists in the parameter list. Both will have the same number of values: if the first has 5 comma-separated values, then the second one also has 5 comma-separated values.

你可以看到我在参数列表中有两个以逗号分隔的值列表。两者将具有相同数量的值：如果第一个具有5个逗号分隔值，则第二个值还具有5个逗号分隔值。

127 and 162 are related
127和162是相关的
204 and 170 are related
204和170是相关的

...and same for the others.

......和其他人一样。

How can I insert these two values? One comma-separated value is inserted, but how do I insert two?

如何插入这两个值？插入一个以逗号分隔的值，但如何插入两个？

3 个解决方案

#1

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:

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

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

“表格值参数不会削减它时，SQL Server 2005及更高版本中的数组和列表”作者：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.

我更喜欢使用数字表方法在TSQL中拆分字符串，但是有很多方法可以在SQL Server中拆分字符串，请参阅前面的链接，它解释了每个链接的PRO和CON。

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:

要使Numbers Table方法起作用，您需要执行一次性表设置，这将创建一个包含1到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:

设置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

You can now easily split a CSV string into a table and join on it. To accomplish your task, set up a test table to insert into:

您现在可以轻松地将CSV字符串拆分为表格并加入其中。要完成您的任务，请设置一个测试表以插入：

create table YourTable (col1 int, col2 int)

then create your procedure:

然后创建你的程序：

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

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
GO

test it out:

测试出来：

exec StoredProcedureName 17,'127,204,110,198',7,'162,170,163,170'
select * from YourTable

OUTPUT:

OUTPUT：

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

(4 row(s) affected)

#2

Have a lok at something like (Full Example)

有一个类似的东西（完整的例子）

DECLARE @Inserts TABLE(
        ID INT,
        Val1 INT,
        Val2 INT,
        Val3 INT
)

DECLARE @Param1 INT,
        @Param2 VARCHAR(100),
        @Param3 INT,
        @Param4 VARCHAR(100)

SELECT  @Param1 = 17,
        @Param2 = '127,204,110,198',
        @Param3 = 7,
        @Param4 = '162,170,163,170'

DECLARE @Table1 TABLE(
        ID INT IDENTITY(1,1),
        Val INT
)

DECLARE @Table2 TABLE(
        ID INT IDENTITY(1,1),
        Val INT
)

DECLARE @textXML XML        

SELECT    @textXML = CAST('<d>' + REPLACE(@Param2, ',', '</d><d>') + '</d>' AS XML)
INSERT INTO @Table1
SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM    @textXML.nodes('/d') T(split)

SELECT    @textXML = CAST('<d>' + REPLACE(@Param4, ',', '</d><d>') + '</d>' AS XML)
INSERT INTO @Table2
SELECT  T.split.value('.', 'nvarchar(max)') AS data
FROM    @textXML.nodes('/d') T(split)

INSERT INTO @Inserts
SELECT @Param1,
        t1.Val,
        @Param3,
        t2.Val
FROM    @Table1 t1 INNER JOIN
        @Table2 t2 ON t1.ID = t2.ID

SELECT *
FROM @Inserts

#3

This may not be an answer to your question... But I thought of letting you know that there is a better way to pass related values (Table Format) to a stored procedure... XML... You can build the XML string in your app (just as regular string) and pass it on to the stored procedure as a parameter... You can then use the following syntax to get it into a table. Hope this helps... In this way you can pass an entire table as parameter to stored procedure...

这可能不是您问题的答案......但我想让您知道有更好的方法将相关值（表格格式）传递给存储过程... XML ...您可以构建XML字符串在您的应用程序中（就像常规字符串一样）并将其作为参数传递给存储过程...然后，您可以使用以下语法将其放入表中。希望这会有所帮助......通过这种方式，您可以将整个表作为参数传递给存储过程...

     --Parameters
    @param1 int,
    @Budgets xml,
    @Param2 int

 -- @Budgets   = '<Values><Row><Val1>127</Val1><Val2>162</Val2></Row>  <Row><Val1>204</Val1><Val2>170</Val2></Row></Values>'

 SELECT @param1 as Param1, 
        x.query('Val1').value('.','int') as val1, 
        @param3 as Param3,
        x.query('Val2').value('.','int') as val1, 
    into #NewTable
    FROM @Budgets.nodes('/Values/Row') x1(x)

#1