I am about to show you a great sql trick that only a few SQL Server T-SQL masters actually know. It will save you a decent amount of coding and make you the envy of your Sql Server colleagues.
Even in the Microsoft documentation on the CASE statement does not list that the CASE … WHEN…THEN…END statement can actuall not only check for logical boolean expressions but via operators but it can also handle an IN() statement similar to a fullblown SQL (or SQL Sub-Query statement, if so inclinded) function in the where clause. The code is quite simple. I have not found any restrictions when using constants (the hard-coded text that does not change) or immediate variables that are defined within the execution frame of the CASE IN() statment such as CASE WHEN something IN(@supertest). That is where @supertest = ‘A, B, C’.
Here is an decent example so enjoy and share with your friends. Be sure to comment on this if you like it. We are trying to gain a rep here at ArtOfBi.com!
SELECT accountID, accountSubID, CASE [debitOrCredit] WHEN 'C' THEN CASE WHEN accountTypeID IN(1, 4, 7) THEN [amount] * -1 ELSE [amount] END WHEN 'D' THEN CASE WHEN accountTypeID IN(2, 3, 6) THEN [amount] * -1 ELSE [amount] END ELSE [amount] END AS amount , CASE dataElement1 IN('asset', 'liability') THEN 'Budget Account' ELSE 'Income Account' END AS something FROM FactDataTable
I have not seen any performance issues with the use of this case statement. It works like a charm for me on tables with 10,000+ records. As a side note, using the CASE IN() statement requires only two rigid assumptions:
- The IN statment must be immediately next to the parenthesis (i.e. no space between the two) ex: IN(‘yes’, ‘no’) and not IN (‘yes’, ‘no’)
- The values must be seperated by a comma. If the value is a string it must be contained as a string in single-tick quotes.