Conversion error for data type varchar to int

Question

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 (@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 

-- Execute the stored procedure with the parameters
EXEC GetNewsOverviewBarData 89567, '1,3,5,7', 'en' ;

error : Conversion failed when converting the varchar value '1,3,5,7' to data type int.

Answer 1

The issue is that you are trying to use a VARCHAR parameter (@unitIds) in the IN clause of your SQL query, which expects a list of integers.

CREATE PROCEDURE [dbo].[GetBarData](
    @userId INT,
    @unitIds VARCHAR(255),
    @language NVARCHAR(2)
)
AS
BEGIN
    DECLARE @UnitIdTable TABLE (UnitId INT);

    INSERT INTO @UnitIdTable
    SELECT CONVERT(INT, value) AS UnitId
    FROM STRING_SPLIT(@unitIds, ',');

    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 UnitId FROM @UnitIdTable)
        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