SQL-Server: Using a switch statement

Sometimes you want queries with conditions and SQL-Server lets you use an if-condition, but sometimes you need to use multiple conditions and not just if-then. If you have any kind of programming background you’ll have been in contact with the switch-statement and it exists in SQL-Server as well. It looks more or less the same in most languages and here’s a neutral example:

var value = 2;
 
SWITCH (value)
	CASE 1:
		//Do something
		break;
 
	CASE 2:
		//Do something else
		break;
 
	CASE 3:
		//Do something spectacular
		break;

If we create a table and call it producs:

CREATE TABLE [dbo].[Products](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Name] [VARCHAR](255) NOT NULL,
	[Price] [DECIMAL](18, 0) NOT NULL,
	[Manufacturer] [VARCHAR](255) NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And then add some data to it and run a standard SELECT query from it we could get:

SELECT [Name], Price, Manufacturer
FROM Products
ORDER BY Price
Name	Price  Manufacturer
Box	1	Aikiyah
Jar	1	Aikiyah
Demo	1	MorkaLork
Tutorial3	MorkaLork
Shelf	3	Aikiyah
Chair	10	Aikiyah
Table	15	Aikiyah

Now, if we want to fetch the data with an extra column where we put the products in three price categories, Expensive, Standard and Cheap, we could use CASE … WHEN to do it:

SELECT
	[Name],
	Price,
	Manufacturer,
	PriceClass =
		CASE
			WHEN Price < 2	THEN 'Cheap'
			WHEN Price >= 2 AND Price < 5 THEN 'Standard'
			WHEN Price >= 5 THEN 'Expensive'
			ELSE 'Okänd'
		END
FROM Products
ORDER BY PriceClass

When we run this query we could get something like this:

Box	1	Cheap
Jar	1	Cheap
Demo	1	Cheap
Chair	10	Expensive
Table	15	Expensive
Shelf	3	Standard
Tutorial3	Standard
Posted in SQL

MySql: if-condition

Sometimes it can be handy to have a if-condition in a SQL query and MySql makes it quite easy. The syntax is quite easy to learn. Here’s an example:

Tabell `Person`:
Id Name Age Gender AddressId 
3	John Doe	50	Male	3
4	Jane Doe	30	Female	4
6	Jake Doe	35	Male	3
7	Jessica Doe	61	Female	4

If we want to fetch all name and prefix them “Mr” or “Mrs” then we could do this:

SELECT IF( Gender = 'Male', CONCAT( 'Mr', ' ', Name ) , CONCAT( 'Mrs', ' ', Name ) ) AS Name
FROM `Person`
WHERE 1
LIMIT 0 , 30

This will fetch all the names and prefix them with Mr if they are male and Mrs if they are female (I’m prudent enough to assume that the names of unmarried women are not stored in a database :))

Name 
Mr John Doe
Mrs Jane Doe
Mr Jake Doe
Mrs Jessica Doe