SQL - ignore where clause if null / no input

Question

I'm building a SSRS report and would like one of my parameters to be optional where data is entered or not.

Here is an example query for a better understanding:

SELECT
 C1
,C2
,C3
FROM
 db_Database..tb_Table
WHERE
 tb_Table_DateTime between [THEN] and [NOW]
 AND
 tb_Table_Integer IN (@Integer)

I'm trying to work out if, in my query, I can ignore the whole:

AND tb_Table_Integer IN (@Integer)

line if user chooses not to input any number.

Bascially, I want all data returned unless specified otherwise via @integer.

If not possible in the query, can this be achieved in the Visual Studio?

Cheers.

Answer 1

This is typically handled by doing:

WHERE . . . AND
      (@Integer IS NULL OR tb_Table_Integer = @Integer)

Do not use IN (@Integer). It sort of implies that you think that @Integer could be a list. That is not possible.

Answer 2

The most common way to do this is with coalesce or nullif. Like this:

WHERE coalesce(@integer,tb_Table_Integer) = tb_Table_Integer