-1

How can I include or exclude data from my result.

I have a table with many values in one row. I want to show all of the value "end" or all values without "end". I also have a variable for my switch "@end".

If it has the value "310" the procedure should show me all with "end". If it has another value the procedure should show me all without "end".

I tried something like this:

where CASE @END WHEN 310 THEN m.LONR = @ENDE ELSE m.LONR <> 310 END

It seems operators are not possible in where clause with case.

I have not found any equivalent answer here or I don't understand.

thanks jens

Jens
  • 3
  • 1

1 Answers1

1

Instead of a case expression, you could simply use OR:

WHERE   (@End = 310 AND m.LONR = 310)
OR      (@End <> 310 AND m.LONR <> 310)

It is worth noting that neither predicate will evaluate to true in the case of NULL values, so if either the variable or the column are nullable you may wish to use:

WHERE   (@End = 310 AND m.LONR = 310)
OR      (ISNULL(@End, 0) <> 310 AND ISNULL(m.LONR, 0) <> 310);

If m.LONR is indexed, then it would almost certainly be beneficial to split the cases up using either IF:

IF (@End = 310)
BEGIN
    SELECT  <columns>
    FROM    <tables>
    WHERE   m.LONR = 310;
END
ELSE
BEGIN
    SELECT  <columns>
    FROM    <tables>
    WHERE   m.LONR <> 310;
END

Or using UNION ALL

SELECT  <columns>
FROM    <tables>
WHERE   m.LONR = 310
AND     @End = 310;
UNION ALL
SELECT  <columns>
FROM    <tables>
WHERE   m.LONR <> 310
AND     @End <> 310;  --OR ISNULL(@End, 0) <> 310 to deal with NULLs

The reason for this is that SQL Server will create a plan based on @End being unknown, therefore does not know at compilation time whether it will be filtering by:

m.LONR = 310

or 

m.LONR <> 310

Therefore it cannot effectively plan whether an index seek, or table scan will be more efficient. Separating the logic will allow the most efficient plan to be used for each scenario.

GarethD
  • 68,045
  • 10
  • 83
  • 123