The SQL Server CASE statement is a conditional operation that can be used to provide if-then-else type of logic to T-SQL queries. The CASE statement can be used in SQL Server queries involving calculations, data transformation, and more.<\/p>\n\n\n\n
The CASE operator in SQL Server is a powerful tool for creating more complex queries, particularly when you want to return different results based on conditions. The CASE statement works like an if-then-else block in other programming languages, allowing you to test conditions and return different results based on those conditions. Indeed, the generic syntax of a CASE statement in T-SQL is represented like this:<\/p>\n\n\n\n
CASE \n WHEN condition1 THEN result1 \n WHEN condition2 THEN result2 \n ...\n ELSE default_result \nEND\n<\/pre>\n\n\n\nCreate the Sample Table<\/em><\/h3>\n\n\n\n
For our examples, let’s create a table
Orders<\/code> and populate it with some data, i.e., 7 lines with different order statuses.<\/p>\n\n\n\n-- Create the Orders table\nCREATE TABLE [dbo].[Orders](\n [OrderID] [int] PRIMARY KEY,\n [CustomerID] [int] NOT NULL,\n [OrderAmount] [decimal](10,2) NOT NULL,\n [OrderStatus] [nvarchar](20) NOT NULL\n);\n\n-- Insert data into the Orders table\nINSERT [dbo].[Orders] VALUES (1, 1, 100.00, 'Processed');\nINSERT [dbo].[Orders] VALUES (2, 2, 200.00, 'Shipped');\nINSERT [dbo].[Orders] VALUES (3, 1, 300.00, 'Returned');\nINSERT [dbo].[Orders] VALUES (4, 3, 150.00, 'Shipped');\nINSERT [dbo].[Orders] VALUES (5, 4, 250.00, 'Processed');\nINSERT [dbo].[Orders] VALUES (6, 2, 350.00, 'Returned');\nINSERT [dbo].[Orders] VALUES (7, 1, 400.00, 'Shipped');\n<\/pre>\n\n\n\n2. Using the CASE Statement in a SELECT query<\/h2>\n\n\n\n
We can use a CASE query in T-SQL within the SELECT clause to change how the data is displayed based on conditions. For example, we can add a new column to our query that shows if the
OrderAmount<\/code> is ‘High’ or ‘Low’:<\/p>\n\n\n\nSELECT OrderID, \n CustomerID, \n OrderAmount,\n OrderStatus,\n CASE \n WHEN OrderAmount > 300 THEN 'High'\n ELSE 'Low'\n END AS OrderValue\nFROM Orders;\n<\/pre>\n\n\n\n3. CASE Statement and WHERE Clause<\/h2>\n\n\n\n
We can use a SQL Server CASE statement in the WHERE section to dynamically filter records. For example, we can filter orders based on their status and amount:<\/p>\n\n\n\n
SELECT OrderID, \n CustomerID, \n OrderAmount,\n OrderStatus\nFROM Orders\nWHERE (CASE \n WHEN OrderStatus = 'Processed' AND OrderAmount > 200 THEN 1\n WHEN OrderStatus = 'Shipped' AND OrderAmount < 200 THEN 1\n ELSE 0\n END) = 1;\n<\/pre>\n\n\n\nThis query will return the orders that are either ‘Processed’ with an amount greater than 200 or ‘Shipped’ with an amount less than 200. On the same topic, here is how to compare data with SQL Server<\/strong><\/a>.<\/p>\n\n\n\n