SQL Server存储过程实现学院及关联表批量数据插入

阿华AIGC实验室

2026-5-9

嘿，这个需求我刚好处理过，给你一个完全符合要求的解决方案，不用循环、临时表或者TVP，一次搞定！

核心思路

利用SQL Server的SCOPE_IDENTITY()获取刚插入的学院ID，再配合STRING_SPLIT（或者兼容低版本的自定义拆分函数）批量拆分前端传的设施ID字符串，一次性插入中间表，全程不需要循环或多次调用。

解决方案代码

假设你的中间表名为CollegeFacilitiesJunction（可根据实际修改），且Colleges表的id是自增主键：

CREATE PROCEDURE InsertCollegeWithFacilities
    @CollegeName NVARCHAR(100),
    @Location NVARCHAR(200),
    @FacilityIDs VARCHAR(MAX) -- 前端传入的逗号分隔设施ID，示例：'2,5,7'
AS
BEGIN
    SET NOCOUNT ON;

    -- 第一步：插入学院数据到Colleges表
    INSERT INTO Colleges (name, location, facilities)
    VALUES (@CollegeName, @Location, NULL); -- 若facilities是冗余字段，建议设为NULL或后续移除

    -- 获取刚插入的学院ID（仅限自增主键场景）
    DECLARE @NewCollegeID INT = SCOPE_IDENTITY();

    -- 第二步：批量插入中间表，拆分设施ID字符串
    INSERT INTO CollegeFacilitiesJunction (college_id, facility_id)
    SELECT 
        @NewCollegeID, 
        CAST(value AS INT)
    FROM STRING_SPLIT(@FacilityIDs, ',')
    WHERE TRY_CAST(value AS INT) IS NOT NULL; -- 过滤无效的非数字ID，避免报错
END
GO

低版本SQL Server兼容方案（低于2016）

如果你的SQL Server版本不支持STRING_SPLIT，可以先创建一个自定义的字符串拆分函数，再替换上面的STRING_SPLIT：

-- 自定义字符串拆分函数（兼容SQL Server 2008及以上）
CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @SplitValues TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT = 1;
    DECLARE @EndIndex INT;

    WHILE CHARINDEX(@Delimiter, @String, @StartIndex) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @String, @StartIndex);
        INSERT INTO @SplitValues (Value)
        SELECT SUBSTRING(@String, @StartIndex, @EndIndex - @StartIndex);
        SET @StartIndex = @EndIndex + 1;
    END

    -- 插入最后一段字符串
    INSERT INTO @SplitValues (Value)
    SELECT SUBSTRING(@String, @StartIndex, LEN(@String) - @StartIndex + 1);

    RETURN;
END
GO

然后修改存储过程中的插入逻辑：

INSERT INTO CollegeFacilitiesJunction (college_id, facility_id)
SELECT 
    @NewCollegeID, 
    CAST(value AS INT)
FROM dbo.SplitString(@FacilityIDs, ',')
WHERE TRY_CAST(value AS INT) IS NOT NULL;