Three conditions on one column of table

问题:

Following is script of sample Table

DECLARE @tbl TABLE (ID int)
INSERT INTO @tbl VALUES(1), (2), (NULL), (3), (NULL), (1)

I want three condition on column ID

SELECT * FROM @tbl WHERE ID -- Can't figure out

1. If User want all rows
2. If user want rows where ID is NULL
3. If user want rows where ID is NOT NULL

I can do this by putting my query in string but In long query there is only one condition like this so I don't want to put all query in string.

EDIT: In response to @Tim Schmelter. My apologies that I could not make my point clear. User will select from front end that either s/he want to all rows only rows where ID is given or rows where ID is not given

In long query one condition is like this

@id INT // Value from front end like 'All', 'Products', 'No Products'
WHERE ID = @ID // Here I can't figure out that how to use one of three conditions

Please let me know If I am still unable to explain my question. I will try again.

Thanks.

回答1:

You can present to user the choices(e.g. radio button), then pass the chosen option's value to database like :

SELECT * FROM @tbl 
WHERE 
   (@Option = 1)
   OR (@Option = 2 AND ID IS NULL)
   OR (@Option = 3 AND ID IS NOT NULL)

OR can kill performance though, SQL Server(or any RDBMS for that matter) doesn't do short-circuit. CASE WHEN can somewhat force short-circuit

SELECT * FROM @tbl 
WHERE 
   CASE @Option
   WHEN 1 THEN 1
   WHEN 2 THEN 
          CASE WHEN ID IS NULL THEN 1 END
   WHEN 3 THEN
          CASE WHEN ID IS NOT NULL THEN 1 END
   END = 1

回答2:

Maybe i've misunderstood your requirement, isn't it that easy?

-- 1.) If User want all rows 
SELECT * FROM @tbl 

-- 2.) If user want rows where ID is NULL
SELECT * FROM @tbl 
WHERE ID IS NULL

-- 3.) If user want rows where ID is NOT NULL
SELECT * FROM @tbl 
WHERE ID IS NOT NULL 

Edit: So you want to use a parameter to filter accordingly, have a look:

SELECT * FROM @tbl 
WHERE   @FilterID = 1                      -- returns all rows
OR    ( @FilterID = 2 AND ID IS NULL)      -- returns all null-rows
OR    ( @FilterID = 3 AND ID IS NOT NULL)  -- returns all not null rows

You can also use CASE in where clause, although it is not recommended.