SQL Server 2005 中的逗号分隔值插入 [英] Comma-separated value insertion In 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'

你可以看到我在参数列表中有两个逗号分隔的值列表.两者都有相同数量的值:如果第一个有 5 个逗号分隔的值，那么第二个也有 5 个逗号分隔的值.

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.

• 127 和 162 是相关的
• 204 和 170 是相关的

...其他人也一样.

如何插入这两个值?插入了一个逗号分隔值，但如何插入两个?

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

推荐答案

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

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:

"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

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

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.

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

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)

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

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 

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

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:

create table YourTable (col1 int, col2 int)

然后创建您的程序:

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

测试一下:

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

输出:

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

(4 row(s) affected)

这篇关于SQL Server 2005 中的逗号分隔值插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！