How to filter a table by a column whose value is included / not included in the list of values?

Question

SQL

When generating SQL from code, only one condition line is available

SELECT * FROM [dbo].[data] WHERE ([dbo].[data].[intField] IN (0,1,2,8,9))[email protected]

when generating, I can only change
([dbo].[data].[intField] IN (0,1,2,8,9)- this piece

in @p1 - bit (bool) is transferred

Answer 1

Option 1:
Sew NOT into the query text, depending on the state of p1:

"SELECT * FROM [dbo].[data] WHERE ([dbo].[data].[intField] " + (p1 ? "" : "NOT") + " IN (0,1,2,8,9))"

Option 2, if MS SQL supports the bool type in SQL, then the following expression:

SELECT * FROM [dbo].[data]
      WHERE @p1 = true and [dbo].[data].[intField] IN (0,1,2,8,9)
              or @p1 = false and [dbo].[data].[intField] NOT IN (0,1,2,8,9)

Option 2.1, if MS SQL does not support the bool type in SQL, then you need to give p1 the value 0/1 instead of false/true, then the expression:

SELECT * FROM [dbo].[data]
          WHERE @p1 = 0 and [dbo].[data].[intField] IN (0,1,2,8,9)
                  or @p1 = 1 and [dbo].[data].[intField] NOT IN (0,1,2,8,9)

Answer 2

SELECT *
  FROM [dbo].[data]
  WHERE ( @p1 = 0 AND [dbo].[data].[intField] NOT IN (0,1,2,8,9) )
     OR ( @p1 = 1 AND [dbo].[data].[intField]     IN (0,1,2,8,9) )