Using IN in a CASE query as one of the possible results-CodePudding

I have a query where I often have to limit to a specific username to find things, otherwise it needs to query from a table of usernames.

I am currently using the following code which ignores my table of usernames:

DECLARE @MyValue CHAR(20)
SET @MyValue = '<All>'

SELECT
    RefNo, Name 
FROM
    table1
WHERE 
    RefNo = CASE WHEN (@MyValue IS NULL) OR (@MyValue = '<All>') 
                THEN RefNo 
                ELSE @MyValue 
            END

Is it possible to use something like the following to have it select from another table of values if the @MyValue value is null?

WHERE
    RefNo = CASE 
                WHEN (@MyValue IS NULL) OR (@MyValue = '<All>') 
                    THEN RefNo IN (SELECT DISTINCT G.RefNo FROM table2 AS G)
                    ELSE @MyValue 
            END

I know it doesn't work as I've written it, hence me asking if this sort of thing is possible. Would using IF statements be a way to do it?

I get this error message using the above code:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I even tried using

AND RefNo in (CASE WHEN (@MyValue IS NULL) OR (@MyValue = '<All>') THEN (SELECT DISTINCT G.RefNo FROM Table2 AS G) ELSE @MyValue END)

But got the same error result.

CodePudding user response：

As @Larnu points out, this requires straightforward AND/OR logic.

WHERE
(
    (@MyValue IS NULL OR @MyValue = '<All>')
    AND RefNo IN (SELECT DISTINCT G.RefNo FROM table2 AS G)
) 
OR RefNo = @MyValue