0

I have a SQL Server 2012 query that takes a parameter @Region. There's a WHERE clause that I want to set its value based on the @Region value. Below is the code I was trying to use

case 
    when @Region = 'SW' 
       then Transact.busloc_id in (3, 7, 11)
       else Transact.busloc_id in (1, 5, 7)
end
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ernie Sal
  • 3
  • 1
  • 1
    Possible duplicate of ["CASE" statement within "WHERE" clause in SQL Server 2008](https://stackoverflow.com/questions/8785209/case-statement-within-where-clause-in-sql-server-2008) – GSerg Dec 30 '19 at 18:15

2 Answers2

2

In a where clause, you want logic like:

where ( @Region = 'SW' and Transact.busloc_id IN (3, 7, 11) ) or
      ( @Region <> 'SW' and Transact.busloc_id IN (1, 5, 7) )

No case expression is necessary.

Note: This assumes that @region is not NULL. That can easily be handled.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This worked great !! Now that I look at think I was overthinking it . THANKS!! – Ernie Sal Dec 30 '19 at 18:33
  • 1
    @ErnieSal . . . I don't understand why you accepted an answer that is about setting a parameter rather than handling logic in the `where` clause. – Gordon Linoff Dec 30 '19 at 19:52
0

One way to set a variable's value involving a CASE statement in SQL has the following syntax:

SET @Variable = CASE WHEN [condition] THEN [scalar_value] ELSE [scalar_value] END

In your SQL you have conditions where scalar values should be. Without further information on what you're trying to achieve with the IN clauses I can't provide more of an answer, however you can go here for more information on the syntax of a CASE statement.

Andrew Simon
  • 148
  • 7