如何改造存储过程以支持多文档ID的文件名规范化处理

嘿，这个场景我太熟了！原来的存储过程之所以只能处理单个DocumentID的情况，大概率是你用了单个变量来存储查询到的DocumentID（比如SELECT @DocumentID = DocumentID FROM Attachment WHERE EntityID = @ID），这种写法如果碰到多个匹配的记录，变量只会保留最后一条的值，自然就丢了其他的DocumentID。

要改成支持多DocumentID的情况，核心思路就是把单值处理改成集合式处理，直接通过关联查询一次性拿到所有对应的数据，再逐个清理文件名就行。下面给你两种可行的修改方案：

方案一：使用自定义字符串清理函数（简洁易维护） #

首先创建一个用于清理文件名的自定义函数，专门负责移除非a-z和数字的字符：

CREATE FUNCTION dbo.CleanFileName(@FileName NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Cleaned NVARCHAR(MAX) = ''
    DECLARE @Index INT = 1
    
    WHILE @Index <= LEN(@FileName)
    BEGIN
        DECLARE @Char CHAR(1) = SUBSTRING(@FileName, @Index, 1)
        -- 只保留小写字母和数字（如果需要保留大写，可加上@Char BETWEEN 'A' AND 'Z'）
        IF (@Char BETWEEN 'a' AND 'z') OR (@Char BETWEEN '0' AND '9')
            SET @Cleaned += @Char
        SET @Index += 1
    END
    
    RETURN @Cleaned
END

然后修改你的存储过程，去掉单个变量的逻辑，直接关联表查询并处理所有记录：

ALTER PROCEDURE [dbo].[NormalizeDocumentFileName1]
    @EntityID INT -- 假设你的输入参数是EntityID，可根据实际调整参数名
AS
BEGIN
    SET NOCOUNT ON;

    -- 关联Attachment和Document表，一次性获取所有匹配的文档并处理文件名
    SELECT
        a.EntityID,
        d.DocumentID,
        d.OriginalFileName, -- 可选：返回原文件名方便对照
        dbo.CleanFileName(d.OriginalFileName) AS NormalizedFileName
    FROM
        Attachment a
    INNER JOIN
        Document d ON a.DocumentID = d.DocumentID
    WHERE
        a.EntityID = @EntityID; -- 根据输入的ID过滤
END

方案亮点： #

• 函数逻辑独立，后续如果需要调整清理规则（比如允许下划线、保留大写字母），直接修改函数即可，不用动存储过程
• 存储过程代码简洁，可读性强

方案二：使用递归CTE（无需额外创建函数） #

如果不想创建单独的函数，可以用递归CTE在存储过程内部完成文件名清理：

ALTER PROCEDURE [dbo].[NormalizeDocumentFileName1]
    @EntityID INT
AS
BEGIN
    SET NOCOUNT ON;

    WITH CleanedFiles AS (
        -- 初始递归：获取所有目标文档，初始化清理后的字符串和索引
        SELECT
            a.EntityID,
            d.DocumentID,
            d.OriginalFileName,
            CAST('' AS NVARCHAR(MAX)) AS NormalizedFileName,
            1 AS IndexPos
        FROM
            Attachment a
        INNER JOIN
            Document d ON a.DocumentID = d.DocumentID
        WHERE
            a.EntityID = @EntityID
        
        UNION ALL
        
        -- 递归处理：逐个字符检查，保留合法字符
        SELECT
            EntityID,
            DocumentID,
            OriginalFileName,
            NormalizedFileName + 
                CASE 
                    WHEN SUBSTRING(OriginalFileName, IndexPos, 1) BETWEEN 'a' AND 'z' 
                        OR SUBSTRING(OriginalFileName, IndexPos, 1) BETWEEN '0' AND '9' 
                    THEN SUBSTRING(OriginalFileName, IndexPos, 1) 
                    ELSE '' 
                END,
            IndexPos + 1
        FROM
            CleanedFiles
        WHERE
            IndexPos <= LEN(OriginalFileName)
    )
    -- 取递归结束后的最终结果
    SELECT
        EntityID,
        DocumentID,
        OriginalFileName,
        NormalizedFileName
    FROM
        CleanedFiles
    WHERE
        IndexPos = LEN(OriginalFileName) + 1
    OPTION (MAXRECURSION 0); -- 解除递归次数限制，适配长文件名
END

方案亮点： #

• 不需要额外创建对象，所有逻辑都在存储过程内部
• 适合临时场景或者权限受限无法创建函数的情况

核心修改点总结 #

1. 移除了原来用单个变量存储DocumentID的逻辑，改用INNER JOIN关联Attachment和Document表，一次性获取所有匹配的记录
2. 将单个文件名的处理逻辑扩展为对每一条记录的批量处理
3. 返回结果集而非单个值，确保所有处理后的文件名都能被调用方获取

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