1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Doogle
  • 13
  • 4
  • 1
    `CASE` is an expression, not a statement. That `WHERE` with the subquery won't generate that error, it would generate the errors *"Incorrect syntax near the keyword 'IN'."* and *"Incorrect syntax near the keyword 'ELSE'."* You need to use explicit `AND` and `OR` logic here. – Thom A Jan 05 '23 at 17:03
  • You can use a `case` expression in a `where` or `on` clause as shown [here](https://stackoverflow.com/a/10260297/92546), but performance will likely suffer. – HABO Jan 05 '23 at 18:32
  • It would appear that `@MyValue IS NULL` will always be false since you set a default value when you declared it. – Mark Schultheiss Jan 05 '23 at 22:11
  • Thank you, I'm still learning this SQL stuff and really appreciate the help. Using AND and OR as Larnu pointed out, and Dale K demonstrated, worked perfectly. – Doogle Jan 20 '23 at 11:17

1 Answers1

2

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
Dale K
  • 25,246
  • 15
  • 42
  • 71