Geek asks and answers

Three conditions for one table column

Below is the table script table

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

I need three conditions for a column ID

SELECT * FROM @tbl WHERE ID -- Can't figure out
  • If the user wants all the lines
  • If the user needs strings where ID is NULL
  • If the user needs strings where the identifier is NOT NULL

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

EDIT: In response to @Tim Schmelter. I apologize that I cannot understand. The user will choose from the front end that either he / she wants all the lines to be only lines in which the identifier is indicated, or lines where the identifier is not specified

In a long query, one condition looks 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

, . .

.

0
2

(, ), , :

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

, SQL Server ( , ) . CASE

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
0

, , ?

-- 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

. , :

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

CASE where, .

+3

More articles:


All Articles