低版本SQL Server实现逗号分隔ID批量插入表的存储过程方案咨询

错误原因 #

你遇到的问题分为两点：

1. 低版本SQL Server（低于2016版本）不支持内置STRING_SPLIT函数，需要自定义拆分逻辑
2. 你自己编写的splitstring函数调用时有两个错误：
   • 函数返回的表字段名为Name，但存储过程中查询时用了不存在的Items字段
   • 你定义的splitstring仅接收1个字符串参数，分隔符是代码写死的逗号，调用时多传了第二个分隔符参数

最简修复方案 #

直接修正存储过程的调用逻辑即可正常运行：

CREATE PROC usp_UpdateStudentAttendance
@StudentIds VARCHAR(max),
@DepartmentId INT
AS
BEGIN
SET NOCOUNT ON; -- 避免返回影响行数的冗余信息，提升性能
INSERT INTO [dbo].[StudentAttendance] ([StudentId], [DepartmentId], [Comments], [Date])
SELECT CAST([Name] AS INT), @DepartmentId, 'Attended', GETDATE() FROM dbo.splitstring(@StudentIds)
END

优化方案 #

优化自定义拆分函数（兼容边界场景） #

如果需要兼容首尾带逗号、中间有空值、自定义分隔符等异常场景，可以优化拆分函数逻辑：

CREATE FUNCTION dbo.splitstring ( 
    @stringToSplit VARCHAR(MAX),
    @delimiter CHAR(1) = ',' -- 支持自定义分隔符，默认用逗号
)
RETURNS @returnList TABLE ([Value] NVARCHAR(500))
AS
BEGIN
    DECLARE @value NVARCHAR(255), @pos INT
    -- 先去除首尾空格和多余的分隔符
    SET @stringToSplit = LTRIM(RTRIM(@stringToSplit))
    IF LEFT(@stringToSplit, 1) = @delimiter SET @stringToSplit = RIGHT(@stringToSplit, LEN(@stringToSplit) - 1)
    IF RIGHT(@stringToSplit, 1) = @delimiter SET @stringToSplit = LEFT(@stringToSplit, LEN(@stringToSplit) - 1)

    WHILE CHARINDEX(@delimiter, @stringToSplit) > 0
    BEGIN
        SET @pos = CHARINDEX(@delimiter, @stringToSplit)
        SET @value = LTRIM(RTRIM(SUBSTRING(@stringToSplit, 1, @pos - 1)))
        -- 自动跳过空值
        IF @value <> '' INSERT INTO @returnList VALUES (@value)
        SET @stringToSplit = SUBSTRING(@stringToSplit, @pos + 1, LEN(@stringToSplit) - @pos)
    END
    -- 插入最后一个拆分结果
    IF LTRIM(RTRIM(@stringToSplit)) <> '' INSERT INTO @returnList VALUES (@stringToSplit)
    RETURN
END

优化后存储过程可以保持原来的传参写法：

CREATE PROC usp_UpdateStudentAttendance
@StudentIds VARCHAR(max),
@DepartmentId INT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[StudentAttendance] ([StudentId], [DepartmentId], [Comments], [Date])
SELECT CAST([Value] AS INT), @DepartmentId, 'Attended', GETDATE() FROM dbo.splitstring(@StudentIds, ',')
END

无自定义函数方案（递归CTE实现） #

如果不方便创建自定义函数，可以直接在存储过程内用CTE递归实现字符串拆分：

CREATE PROC usp_UpdateStudentAttendance
@StudentIds VARCHAR(max),
@DepartmentId INT
AS
BEGIN
SET NOCOUNT ON;
WITH SplitCTE AS (
    SELECT 
        CAST(LEFT(@StudentIds, CHARINDEX(',', @StudentIds + ',') - 1) AS INT) AS StudentId,
        STUFF(@StudentIds, 1, CHARINDEX(',', @StudentIds + ','), '') AS Remainder
    UNION ALL
    SELECT 
        CAST(LEFT(Remainder, CHARINDEX(',', Remainder + ',') - 1) AS INT),
        STUFF(Remainder, 1, CHARINDEX(',', Remainder + ','), '')
    FROM SplitCTE
    WHERE Remainder > ''
)
INSERT INTO [dbo].[StudentAttendance] ([StudentId], [DepartmentId], [Comments], [Date])
SELECT StudentId, @DepartmentId, 'Attended', GETDATE() FROM SplitCTE
OPTION (MAXRECURSION 0); -- 取消递归层级限制，支持超长学生ID列表
END

内容的提问来源于stack exchange，提问作者user187