This article describes how to use the
T-SQL Expressions in SQL Server database.
We will discuss in detail the three main types of SQL Server expressions namely CASE, COALESCE and NULLIF.
Types of Expressions
What is CASE expression in SQL Server?
The CASE statement in SQL Server returns a specific output of value based on the condition specified by the user.
The SQL Server CASE statement has at least one pair of WHEN-THEN statements.
The WHEN statement consists of the condition to be applied to the table and tested, and the THEN statement return the input expression once the WHEN statement returns TRUE. This also has an ELSE statement, but it is optional. The ELSE statement has the activity to be done once any of the WHEN statements don't return TRUE. Finally, the case statement ends with the END keyword.
The syntax for the CASE statement
SELECT column1, column2,
CASE WHEN condition_expression THEN result_expression
END AS column_name
SELECT name, price,
CASE WHEN price = 30 THEN 'B1'
ELSE 'B2' END AS case_result
What is COALESCE expression in SQL Server?
COALESCE expression in SQL Server has string manipulations.
The method of obtaining another form using the existing data is called String Manipulation.
COALESCE expression will return the data type which has the highest precedence data type. If the entered expressions are nullable, the output will also be nullable.
The syntax for COALESCE statement
COALESCE ( expression [ 1…n ] )
The properties of COALESCE
Expressions should be of the same type
There can be multiple expressions
First, check the integer, consisting of a character expression after the integer, which will give an integer as an output.
SELECT COALESCE (NULL,' T1', 'T2');
SELECT COALESCE (NULL,100,25,30,50);
SELECT COALESCE (NULL,NULL,22,NULL,NULL);
SELECT COALESCE (NULL,NULL,NULL,NULL,10,'Test');
SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Test');
What is the NULLIF expression
NULLIF expression in SQL Server returns a NULL value when the given two parameters are identical, and if it is not identical, it will return the value of the first parameter.
The syntax for NULLIF statement
NULLIF(current_month, previous_month) AS LastLog