Is there a way to add a logical Operator in a WHERE clause using CASE statements? - T-SQL

Question

I searched the web but cannot find a solution for my problem (but perhaps I am using the wrong keywords ;) ).

I've got a Stored Procedure which does some automatic validation (every night) for a bunch of records. However, sometimes a user wants to do the same validation for a single record manually. I thought about calling the Stored Procedure with a parameter, when set the original SELECT statement (which loops through all the records) should get an AND operator with the specified record ID. I want to do it this way so that I don't have to copy the entire select statement and modify it just for the manual part.

The original statement is as follows:

DECLARE GenerateFacturen CURSOR LOCAL FOR
    SELECT TOP 100 PERCENT becode, dtreknr, franchisebecode, franchisenemer, fakgroep, vonummer, vovolgnr, count(*) as nrVerOrd,
                                            FaktuurEindeMaand, FaktuurEindeWeek
        FROM (
           SELECT becode, vonummer, vovolgnr, FaktuurEindeMaand, FaktuurEindeWeek, uitgestfaktuurdat, levdat, voomschrijving, vonetto,
                                faktureerperorder, dtreknr, franchisebecode, franchisenemer, fakgroep, levscandat
              FROM vwOpenVerOrd WHERE becode=@BecondeIN AND levdat IS NOT NULL AND fakstatus = 0 
              AND isAllFaktuurStukPrijsChecked = 1 AND IsAllFaktuurVrChecked = 1
              AND (uitgestfaktuurdat IS NULL OR uitgestfaktuurdat<=@FactuurDate)
                        ) sub   
              WHERE faktureerperorder = 1
              GROUP BY becode, dtreknr, franchisebecode, franchisenemer, fakgroep, vonummer, vovolgnr,
                                FaktuurEindeMaand, FaktuurEindeWeek
              ORDER BY MIN(levscandat)

At the WHERE faktureerperorder = 1 I came up with something like this:

WHERE faktureerperorder = 1 AND CASE WHEN @myParameterManual = 1 THEN vonummer=@vonummer ELSE 1=1 END

But this doesn't work. The @myParameterManual indicates whether or not it should select only a specific record. The vonummer=@vonummer is the record's ID. I thought by setting 1=1 I would get all the records.

Any ideas how to achieve my goal (perhaps more efficient ideas or better ideas)?

Answer 1

I'm finding it difficult to read your query, but this is hopefully a simple example of what you're trying to achieve.

I've used a WHERE clause with an OR operator to give you 2 options on the filter. Using the same query you will get different outputs depending on the filter value:

CREATE TABLE #test ( id INT, val INT );

INSERT  INTO #test
        ( id, val )
VALUES  ( 1, 10 ),
        ( 2, 20 ),
        ( 3, 30 );

DECLARE @filter INT; 

-- null filter returns all rows
SET @filter = NULL;

SELECT  *
FROM    #test
WHERE   ( @filter IS NULL
          AND id < 5
        )
        OR ( @filter IS NOT NULL
             AND id = @filter
           );

-- filter a specific record
SET @filter = 2;

SELECT  *
FROM    #test
WHERE   ( @filter IS NULL
          AND id < 5
        )
        OR ( @filter IS NOT NULL
             AND id = @filter
           );

DROP TABLE #test;

First query returns all:

id  val
1   10
2   20
3   30

Second query returns a single row:

id  val
2   20