SQL: How to show/hide inactive users based on parameter

Question

In sql server, I have a simple query that returns all users, both active & inactive. I need to update my query to provide the user with an option to show or hide inactive users. That user's value will be passed in via a parameter called "@ShowInactiveUsers" (BIT). I have a few ways of going about doing this, but I'm looking for the best way to set this up. So, in my below query, what's the best way to setup my WHERE clause so that if ShowInactiveUsers = false, then only show results where Active = 1. But if ShowInactiveUsers = true, then show all results?

DECLARE @ShowInactiveUsers bit = 1

 SELECT UserId
        , UserName
        , FullName
        , Active
 FROM Users
 WHERE
    <insert active/inactive where clause here>

Possible duplicate of [T-SQL use different WHERE clause based on value of input parameter](https://stackoverflow.com/questions/44875190/t-sql-use-different-where-clause-based-on-value-of-input-parameter) — Tab Alleman, May 31 '18 at 18:39

score 2 · Accepted Answer · answered May 31 '18 at 18:15

2

@ShowInactiveUsers = 1 OR Active = 1

is true for any row, when @ShowInactiveUsers = 1 and only true for rows where Active = 1 when @ShowInactiveUsers = 0.

But best is relative to what your criteria are.

answered May 31 '18 at 18:15

sticky bit

36,626
12
31
42

score 1 · Answer 2 · answered May 31 '18 at 18:28

You could also use Dynamic SQL inside the procedure to build the query depending on the parameter value you pass to the procedure.

It looks something like this:

DECLARE @ShowInactiveUsers BIT = NULL
DECLARE @SQL NVARCHAR(MAX), @Where NVARCHAR(MAX) 
SET @SQL = 'SELECT UserId
        , UserName
        , FullName
        , Active
 FROM Users WHERE 1=1'

IF (@ShowInactiveUsers = 0) /*means do not show inactive users and show only active users*/
BEGIN
    SET @Where = ' AND Active = 1'
    SET @SQL = @SQL + @Where
END 
PRINT @SQL

EXEC sp_executesql @SQL

SQL: How to show/hide inactive users based on parameter

2 Answers2