执行SQL存储过程时出现VARCHAR转INT类型转换失败错误

错误出现在tn.unitId IN (@unitIds)这一行。@unitIds是字符串类型参数，传入值为'1,3,5,7'，但tn.unitId为整数类型。SQL Server会尝试将整个字符串转换为整数，而非自动拆分出多个独立整数值，因此触发类型转换失败错误。

方法1：使用内置STRING_SPLIT函数（SQL Server 2016及以上版本） #

STRING_SPLIT可将逗号分隔字符串拆分为多行，只需将拆分后的字符串值转换为整数即可：

修改存储过程的WHERE条件部分：

WHERE 
    tn.unitId IN (SELECT CAST(value AS INT) FROM STRING_SPLIT(@unitIds, ','))
    AND (tn.validTo IS NULL OR tn.validTo > GETDATE())
    AND ((tn.priority = 1 AND (tnl.inInfoList = 1 OR tnl.inInfoList IS NULL)) OR tn.priority = 2)

完整存储过程代码：

CREATE PROCEDURE [dbo].[GetBarData](
    @userId INT,
    @unitIds VARCHAR(255),
    @language NVARCHAR(2)
)
AS
BEGIN
    SELECT tn.rid AS newsRid
    FROM tNews tn
    LEFT JOIN tNewsLog tnl ON tn.rid = tnl.newsId AND tnl.userId = @userId
    WHERE 
        tn.unitId IN (SELECT CAST(value AS INT) FROM STRING_SPLIT(@unitIds, ','))
        AND (tn.validTo IS NULL OR tn.validTo > GETDATE())
        AND ((tn.priority = 1 AND (tnl.inInfoList = 1 OR tnl.inInfoList IS NULL)) OR tn.priority = 2)
    ORDER BY tn.priority DESC, shortDescription ASC;
END 

方法2：自定义字符串拆分函数（适用于SQL Server 2016以下版本） #

若你的SQL Server版本不支持STRING_SPLIT，可创建自定义拆分函数：

CREATE FUNCTION dbo.SplitString
(
    @InputString VARCHAR(MAX),
    @Delimiter VARCHAR(5)
)
RETURNS @OutputTable TABLE (Value INT)
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT

    SET @StartIndex = 1
    IF SUBSTRING(@InputString, LEN(@InputString) - 1, LEN(@InputString)) <> @Delimiter
    BEGIN
        SET @InputString = @InputString + @Delimiter
    END

    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString)
        
        INSERT INTO @OutputTable(Value)
        VALUES(CAST(SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex) AS INT))
        
        SET @InputString = SUBSTRING(@InputString, @EndIndex + 1, LEN(@InputString))
    END

    RETURN
END

随后修改存储过程的WHERE条件：

WHERE 
    tn.unitId IN (SELECT Value FROM dbo.SplitString(@unitIds, ','))
    AND (tn.validTo IS NULL OR tn.validTo > GETDATE())
    AND ((tn.priority = 1 AND (tnl.inInfoList = 1 OR tnl.inInfoList IS NULL)) OR tn.priority = 2)

注意事项 #

• 确保传入的@unitIds仅包含有效整数和逗号，避免非数字字符引发转换错误。
• 若需防范SQL注入，可在拆分后添加验证逻辑，检查所有拆分值是否为整数。

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