Using case Statement in SQL with parameter / variable to check for Null values

Question

I am trying to write a SQL Select statement to return records based on user input through an interface. I want to write a Select statement as follows:

SELECT somefields
FROM sometable
WHERE CASE variable
         WHEN 'blank' THEN field IS NULL
         ELSE field = field
      END

Basically, I either want the filter column to find NULL values ​​or ignore the filter, and return all the values ​​depending on the value of the variable. I know that the results of the CASE statement are not executed, but how can I do this?

Answer 1

variable "", , field - NULL. variable - - , :

SELECT somefields
FROM sometable
WHERE
    (variable = 'blank' AND field IS NULL)
    OR (variable <> 'blank')

Answer 2

NULLIF() ( SQL Server, NULLIF() ):

SELECT somefields
  FROM sometable
 WHERE field = NULLIF(variable, 'blank')

Answer 3

:

• @variable is null,
• @variable = 'blank', , field is null field = 'blank'
• , @variable field

:

WHERE 1 = CASE
      WHEN @variable is null then 1
      WHEN @variable = 'blank' and field is null then 1
      WHEN @variable = field then 1
      END

Answer 4

SELECT somefields
FROM sometable
WHERE ((variable IS NULL OR variable = 0) OR (variable = field))

WHERE ,
:

DECLARE @CityName VARCHAR(50)
SET @CityName = NULL

SELECT CityName
FROM City
WHERE ((@CityName IS NULL ) OR (@CityName = CityName ))

City null,

Answer 5

, , . - ?

SELECT field1,
    field2,
    CASE variable
        WHEN 'blank' THEN NULL
        ELSE field3
    END as field3
FROM sometable

Answer 6

, , ... ....

    SELECT 
          House, Postcode 
    from 
          SomeTable 
    where
          (House=isnull(@House,House) or (House is null and @House is null))
    and
          (Postcode=isnull(@Postcode,Postcode) or (Postcode is null and @Postcode is null))

, , ( isnull , )

, , null, no = null, .

Confused? . , , !

Answer 7

, @Andomar , -, varchar, :

FIELD1 = CASE
  WHEN @inputParameter = '' THEN FIELD1
  WHEN @inputParameter <> FIELD1 THEN NULL  -- if input is some text but does not match
  WHEN @inputParameter IS NULL THEN FIELD1
  WHEN @inputParameter != '' AND FIELD1 = @inputParameter THEN FIELD1
END

, -.