SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- the original code fails because tinyint can't compare to nvarchar(max)
DECLARE @str NVARCHAR(50) = 'al',
        @int TINYINT = 1;

SELECT * FROM STRING_SPLIT('123,all', ',')
WHERE @str = 'all' OR @int = value;
Msg 245 Level 16 State 1 Line 5
Conversion failed when converting the varchar value 'all' to data type int.

-- this is false success - lucky in this case the OR isn't fully evaluated
DECLARE @str NVARCHAR(50) = 'all',
        @int TINYINT = 1;

SELECT * FROM STRING_SPLIT('123,all', ',')
WHERE @str = 'all' OR @int = value;

value

123

all

-- you could convert to avoid the problem
DECLARE @str NVARCHAR(50) = 'al',
        @int TINYINT = 1;

SELECT * FROM STRING_SPLIT('123,all', ',')
WHERE @str = 'all' OR CONVERT(nvarchar(max), @int) = value;

value

DECLARE @str NVARCHAR(50) = 'all',
        @int TINYINT = 1

SELECT * FROM STRING_SPLIT('123,all', ',')
WHERE @str = 'all' OR CONVERT(nvarchar(max), @int) = value;

value

123

all

-- or just don't declare it as a tinyint in the first place
DECLARE @str NVARCHAR(50) = 'al',
        @int nvarchar(max) = 1;

SELECT * FROM STRING_SPLIT('123,all', ',')
WHERE @str = 'all' OR @int = value;

value

DECLARE @str NVARCHAR(50) = 'all',
        @int nvarchar(max) = 1;

SELECT * FROM STRING_SPLIT('123,all', ',')
WHERE @str = 'all' OR @int = value;

value

123

all