Certain conditions are checked with the WHEN Statement when using the CASE Statement, and certain results are returned if the condition is met. We can define more than one WHEN Statement in a CASE Statement. If no conditions are met in the WHEN Statement defined in the CASE Statement, then the operations in the ELSE Statement are performed.
Let’s make an example for a better understanding of the subject.
Create a table as follows and add a few records to this table.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE [dbo].[People]( [Name] [varchar](50) NOT NULL, [Age] [smallint] NOT NULL ) ON [PRIMARY] INSERT INTO [dbo].[People] ([Name],[Age]) VALUES ('Nurullah',34), ('Faruk',27), ('Hasan',54), ('Omer',2), ('Suleyman',64), ('Kerem',-1) |
With the help of the following script, we group the people in the People table according to their age.
In the first WHEN Statement, if the age is between 0 and 3, we include it in the “Baby” group.
In the second WHEN Statement, if the age is between 4 and 15, we include it in the “Child” group.
In the third WHEN Statement, if the age is between 16 and 30, we include it in the “Teen” group.
In the Fourth WHEN Statement, if the age is between 31 and 60, we include it in the “Middle Aged” group.
In the fifth WHEN Statement, we add it to the “Old” group if it is over 60 years old.
If the age of the people in the table is not within this range, we return the value as “No age range found.”.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [Name], [Age], CASE WHEN [Age] BETWEEN 0 AND 3 THEN 'Baby' WHEN [Age] BETWEEN 4 AND 15 THEN 'Child' WHEN [Age] BETWEEN 16 AND 30 THEN 'Teen' WHEN [Age] BETWEEN 31 AND 60 THEN 'Middle Aged' WHEN [Age] >60 THEN 'Old' ELSE 'No age range found.' END AS AreYouOld FROM [dbo].[People]; |
