ER5ZKDZ1

1DROP TABLE IF EXISTS [dbo].[Document]
2IF OBJECT_ID('[dbo].[Document]', 'U') IS NULL
3BEGIN
4    CREATE TABLE [dbo].[Document] (
5        [DocumentId] bigint IDENTITY(1,1) NOT NULL
6        ,[DocumentSeriesId] [tinyint] NOT NULL
7        ,CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED ([DocumentId] ASC)
8        ,INDEX [IX_Document_SeriesId] NONCLUSTERED ([DocumentSeriesId] ASC)
9    );
10END;
11GO
12	
13SET IDENTITY_INSERT [dbo].[Document] ON;
14;WITH [DocumentSeed] AS (
15    SELECT
16        1 AS [DocumentId]
17    UNION ALL
18    SELECT
19        [DocumentId] + 1
20    FROM
21        [DocumentSeed]
22    WHERE
23        [DocumentId] < 2048)
24
25INSERT INTO [dbo].[Document] ([DocumentId], [DocumentSeriesId])
26SELECT
27    [DocumentId]
28    ,ABS(CHECKSUM(NEWID()) % 4) + 1
29FROM
30    [DocumentSeed] OPTION (MAXRECURSION 2048);
31SET IDENTITY_INSERT [dbo].[Document] OFF;
32	
33-- Specify the Document Series to be returned.
34DECLARE @Series varchar(12) = 'S1|S2'
35-- Split the user input and insert it into a table variable.
36DECLARE @SeriesSplit table ([SeriesId] tinyint, [Series] varchar(2))
37BEGIN
38INSERT INTO @SeriesSplit ([SeriesId], [Series])
39SELECT
40    CASE [value] WHEN 'S1' THEN 1 WHEN 'S2' THEN 2 WHEN 'S3' THEN 3 WHEN 'S4' THEN 4 ELSE 5 END AS [SeriesId]
41    ,LTRIM(RTRIM([value])) AS [Series]
42FROM
43    string_split(@Series,'|')
44WHERE
45    [value] <> ''
46END;
47
48-- Return the result set of desired [DocumentSeriesId]
49-- In the real use case, DISTINCT is not used and more columns are returned.
50-- However, to illustrate the issue at hand, return only the [DocumentSeriesId] as this is what we are filtering off.
51SELECT DISTINCT
52    D1.[DocumentSeriesId]
53FROM
54    [dbo].[Document] D1
55WHERE
56    D1.[DocumentSeriesId] IN (SELECT SS.[SeriesId] FROM @SeriesSplit SS)
57	
58-- Specify the Document Series to be returned.
59DECLARE @Series varchar(14) = 'S1|S2'
60-- Split the user input and insert it into a table variable.
61DECLARE @SeriesSplit table ([SeriesId] tinyint, [Series] varchar(4))
62BEGIN
63INSERT INTO @SeriesSplit ([SeriesId], [Series])
64SELECT
65    CASE [value] WHEN 'S1' THEN 1 WHEN 'S2' THEN 2 WHEN 'S3' THEN 3 WHEN 'S4' THEN 4 ELSE 5 END AS [SeriesId]
66    ,LTRIM(RTRIM([value])) AS [Series]
67FROM
68    string_split(@Series,'|')
69WHERE
70    [value] <> ''
71END;
72
73-- Return the result set of desired [DocumentSeriesId]
74-- In the real use case, DISTINCT is not used and more columns are returned.
75-- However, to illustrate the issue at hand, return only the [DocumentSeriesId] as this is what we are filtering off.
76SELECT DISTINCT
77    D1.[DocumentSeriesId]
78FROM
79    [dbo].[Document] D1
80WHERE
81    EXISTS (SELECT SS.[SeriesId] FROM @SeriesSplit SS JOIN [dbo].[Document] D2 ON SS.[SeriesId] = D2.[DocumentSeriesId])