我在使用SQL Server 2014，因此没有可用的字符串分割函数。

我有两个用分隔符分隔的字符串（Brand 和 Location），需要在存储过程中插入，以在目标表中创建（可能的）多行。

这是我的存储过程：

ALTER PROCEDURE [dbo].[Add_Reviewer]
     @FirstName NVARCHAR(MAX)
    ,@LastName NVARCHAR(MAX)
    ,@Email NVARCHAR(MAX)
    ,@Brand NVARCHAR(MAX)
    ,@Location NVARCHAR(MAX)
    ,@Department NVARCHAR(MAX)
    ,@ProductLine NVARCHAR(MAX)
AS
BEGIN
    WHILE CHARINDEX('|', @Location) > 0 
    BEGIN
        DECLARE @templocation VARCHAR(max)
        SET @templocation = SUBSTRING(@Location, 1, ( CHARINDEX('|', @Location) - 1 ))

        WHILE CHARINDEX('|', @Brand) > 0 
        BEGIN
            DECLARE @tempbrand VARCHAR(max)
            SET @tempbrand = SUBSTRING(@Brand, 1, ( CHARINDEX('|', @Brand) - 1 ))

             INSERT INTO [Reviewers] ([FirstName], [LastName],
                                      [Email], [Brand], [Location],
                                      [ProductLine], [Department]) 
             VALUES (@FirstName, @LastName,
                     @Email, @tempbrand, @templocation,
                     @Productline, @Department)

             SET @brand = SUBSTRING(@brand, CHARINDEX('|', @brand) + 1, LEN(@brand))
        END

        SET @location = SUBSTRING(@location, CHARINDEX('|', @location) + 1, LEN(@location))
    END
END

我的数据：

EXEC    @return_value = [dbo].[Add_Reviewer]
        @FirstName = N'John',
        @LastName = N'Doe',
        @Email = N'[email protected]',
        @Brand = N'9|10|11|',
        @Location = N'67|56|81|',
        @Department = N'1',
        @ProductLine = N'1'

我预计会得到9 行数据，覆盖3 个地点和3 个品牌：

John | Doe | jdoetest.com |  9 | 67 | 1 | 1
John | Doe | jdoetest.com | 10 | 67 | 1 | 1
John | Doe | jdoetest.com | 11 | 67 | 1 | 1
John | Doe | jdoetest.com |  9 | 56 | 1 | 1
John | Doe | jdoetest.com | 10 | 56 | 1 | 1
John | Doe | jdoetest.com | 11 | 56 | 1 | 1
John | Doe | jdoetest.com |  9 | 81 | 1 | 1
John | Doe | jdoetest.com | 10 | 81 | 1 | 1
John | Doe | jdoetest.com | 11 | 81 | 1 | 1

但结果只有3 行：

John | Doe | jdoetest.com |  9 | 67 | 1 | 1 
John | Doe | jdoetest.com | 10 | 67 | 1 | 1 
John | Doe | jdoetest.com | 11 | 67 | 1 | 1

解决方案

当 STRING_SPLIT 不可用时，我该怎么办？
在早期版本的SQL Server中，有很多关于如何在没有 STRING_SPLIT 的情况下实现的示例。

再举一个例子。

ALTER PROCEDURE Add_Reviewer
     @FirstName NVARCHAR(MAX)
    ,@LastName NVARCHAR(MAX)
    ,@Email NVARCHAR(MAX)
    ,@Brand NVARCHAR(MAX)
    ,@Location NVARCHAR(MAX)
    ,@Department NVARCHAR(MAX)
    ,@ProductLine NVARCHAR(MAX)
AS
BEGIN
    DECLARE @xml_brand AS XML,@xml_location AS XML, @delimiter AS VARCHAR(10);
    SET @delimiter = '|';
    SET @xml_brand = CAST(('<X>' + REPLACE(@Brand, @delimiter, '</X><X>') + '</X>') AS XML);
    SET @xml_location = CAST(('<X>' + REPLACE(@Location, @delimiter, '</X><X>') + '</X>') AS XML);

    INSERT INTO [Reviewers] ([FirstName],[LastName] ,[Email],[Brand],[Location],[ProductLine],[Department]        ) 
    SELECT  @FirstName FirstName, @LastName LastName, @Email Email
      ,b.value('.', 'VARCHAR(20)') AS brand
      ,l.value('.', 'VARCHAR(20)') AS location
      ,@ProductLine ProductLine
      ,@Department Department
    FROM @xml_brand.nodes('X') AS t1(b)
    CROSS JOIN @xml_location.nodes('X') AS t2(l)
    WHERE len(b.value('.', 'VARCHAR(20)'))>0 and len(l.value('.', 'VARCHAR(20)'))>0

END
GO

使用存储过程

exec Add_Reviewer -- EXEC    @return_value = [dbo].[Add_Reviewer]
        @FirstName = N'John',
        @LastName = N'Doe',
        @Email = N'[email protected]',
        @Brand = N'9|10|11|',
        @Location = N'67|56|81|',
        @Department = N'1',
        @ProductLine = N'1'

以下的WHERE子句

WHERE len(b.value('.', 'VARCHAR(20)'))>0 and len(l.value('.', 'VARCHAR(20)'))>0

是必需的，尽管你的参数带有一个“额外的分隔符”。'9|10|11|' 与通常的 '9|10|11' 不同。

演示