存储过程报错：varchar转int类型转换错误，求排查原因

你遇到的这个类型转换报错，根源其实很明确——你在存储过程里用了RETURN @secgroup来返回字符串类型的安全组名称，但SQL Server的RETURN语句只支持返回整数。当数据库尝试把你的varchar类型组名转换成整数时，自然就抛出了转换错误。

为什么会这样？ #

在SQL Server中，RETURN的设计目的是返回存储过程的执行状态码（比如0表示成功，非0表示失败），它只能接受整数类型的值。你这里试图返回varchar(20)类型的@secgroup，数据库会自动触发隐式类型转换，而字符串显然无法转成整数，于是报错。

修复方案 #

这里提供几种可行的修复方式，你可以根据自己的需求选择：

方案1：直接用SELECT返回结果集（最简单）

把RETURN @secgroup替换成SELECT语句返回匹配到的组名，同时跳出循环避免多余执行：

ALTER PROCEDURE [dbo].[report_bus_unit_access] 
    @rpt_path NVARCHAR(200) 
AS 
BEGIN 
    SET NOCOUNT ON; 
    DECLARE @rowcount int; 
    DECLARE @i int; 
    DECLARE @secgroup varchar(20); 
    SET @i = 1; 

    SELECT id = identity(int, 1, 1), a.* 
    INTO #secgroup 
    FROM (
        SELECT DISTINCT security_group 
        FROM BPOCOM.dbo.report_groups 
        WHERE security_group IS NOT NULL
    ) AS a 

    SELECT @rowcount = COUNT(1) FROM #secgroup; 

    WHILE (@i <= @rowcount) 
    BEGIN 
        SELECT @secgroup = security_group FROM #secgroup WHERE id = @i 
        IF PATINDEX('%' + LOWER(@secgroup) + '%', LOWER(@rpt_path)) <> 0 
        BEGIN 
            -- 用SELECT返回匹配结果，替代RETURN
            SELECT @secgroup AS matched_security_group;
            BREAK; -- 找到匹配后立即跳出循环
        END 
        SET @i = @i + 1 
    END 

    -- 处理没有匹配到的情况，返回NULL
    IF @i > @rowcount
    BEGIN
        SELECT NULL AS matched_security_group;
    END
END

方案2：使用OUTPUT参数返回结果

如果你需要通过参数获取返回值（而不是结果集），可以定义一个OUTPUT参数：

ALTER PROCEDURE [dbo].[report_bus_unit_access] 
    @rpt_path NVARCHAR(200),
    @matched_secgroup VARCHAR(20) OUTPUT -- 新增输出参数
AS 
BEGIN 
    SET NOCOUNT ON; 
    DECLARE @rowcount int; 
    DECLARE @i int; 
    DECLARE @secgroup varchar(20); 
    SET @i = 1; 
    SET @matched_secgroup = NULL; -- 初始化参数

    SELECT id = identity(int, 1, 1), a.* 
    INTO #secgroup 
    FROM (
        SELECT DISTINCT security_group 
        FROM BPOCOM.dbo.report_groups 
        WHERE security_group IS NOT NULL
    ) AS a 

    SELECT @rowcount = COUNT(1) FROM #secgroup; 

    WHILE (@i <= @rowcount) 
    BEGIN 
        SELECT @secgroup = security_group FROM #secgroup WHERE id = @i 
        IF PATINDEX('%' + LOWER(@secgroup) + '%', LOWER(@rpt_path)) <> 0 
        BEGIN 
            SET @matched_secgroup = @secgroup;
            BREAK;
        END 
        SET @i = @i + 1 
    END 
END

调用示例：

DECLARE @result VARCHAR(20);
EXEC [dbo].[report_bus_unit_access] 'http://reportserver/Reports/report/Accounting', @result OUTPUT;
SELECT @result AS matched_security_group;

方案3：优化代码——去掉循环，用一次查询搞定（更高效）

原代码的循环写法效率不高，其实可以直接通过查询匹配，还能避免循环带来的性能问题，甚至可以优先匹配更长的组名（防止短名称误匹配）：

ALTER PROCEDURE [dbo].[report_bus_unit_access] 
    @rpt_path NVARCHAR(200) 
AS 
BEGIN 
    SET NOCOUNT ON; 

    -- 直接查询匹配的安全组，优先返回更长的名称
    SELECT TOP 1 security_group AS matched_security_group
    FROM (
        SELECT DISTINCT security_group 
        FROM BPOCOM.dbo.report_groups 
        WHERE security_group IS NOT NULL
    ) AS a
    WHERE PATINDEX('%' + LOWER(a.security_group) + '%', LOWER(@rpt_path)) <> 0
    ORDER BY LEN(a.security_group) DESC;
END

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