Good afternoon everyone! Hope you're all well.

Today's quick blog post is about searched CASE statements, which allows comparison operators to be used. This means that instead of using only equivalent values, the case can search between values. Boolean expressions can also be used so you can determine whether a flag has been switched on or off.

Searched CASE statement using comparison operators

An example of a searched CASE statement using comparison operators is as follows:

This example declares a variable called "Score" and gives it a value. The CASE statement will then work out whether the value is found within the range of the case. If it does, then a message would be displayed to the user. If not, then the next comparison will take place. This continues, much like within a Simple CASE statement, until either a solution has been found or until the transaction returns what has been included in the "ELSE" statement.

Searched CASE statement using Boolean expressions

An example of a searched CASE statement using Boolean expressions is as follows:

This CASE statement example determines whether a value stored within a variable is either Odd or Even. Note that you don't just need to use OR within a Boolean expression CASE statement, you can also use AND, BETWEEN, IN, ALL, ANY, EXISTS, LIKE, NOT and SOME.

What Next...

Tomorrow, I will be writing about IF... ELSE statements, which will be the first of my transaction flow blog posts. Stay tuned!

Thanks for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there. Many Thanks!

Good afternoon guys! Hope you're all well. Over the next few days - I will be working more on my development side of things (ASP.NET and C#) so I thought that this would be the ideal opportunity to run some smaller blogs on Simple Case Statements and some SQL Code Flow commands that you will most certainly come across.

Before I begin...

You must note that CASE statements are not used to control the execution flow of your transaction statements. Instead, it evaluates all conditions sequentially and stops when the first condition is satisfied, similarly to a switch case in other programming languages. The only difference is that you don't actually use the SWITCH keyword.

A simple CASE statement only allows 1 equality check, and it will pick the first satisfied condition before breaking out of the CASE statement and moving on. If none of your conditions are satisfied, it will look for an ELSE clause. Make sure you don't forget to declare your ELSE clause, otherwise it will just return NULL (and most of the time you wouldn't want to return that). Also, you must always remember finish your CASE statement with an END clause, otherwise you will receive the following error message:

Simple CASE Statement Example

Below is a quick and simple example of a CASE statement syntax within a SELECT statement:

Of course, there are lots of other places you can use them, however they tend to work as a searchable CASE statement rather than a simple CASE statement.

What Next...

Tomorrow, I will be writing about searchable CASE statements, which are slightly more complex. Thanks for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there. Many Thanks!