myITSchools

Conditional statements in SQL server

Conditional statements in SQL server allow you to define different logics and actions for different conditions. The main types of conditional statements in SQL server are:

  • IF…ELSE: This statement executes a block of code if a condition is true, and another block of code if the condition is false. For example, you can use IF…ELSE to check if a table exists before creating it.
  • CASE: This expression returns a value based on a set of conditions. It is similar to a switch statement in other programming languages. For example, you can use CASE to assign different labels to customers based on their order amount.

How to use Case Statement in SQL Server?

The CASE statement in SQL Server is used to return a value based on a set of conditions. It is similar to a switch statement in other programming languages. The syntax of the CASE statement is:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

Here, condition1, condition2, etc. are the conditions to be tested, and result1, result2, etc. are the values to be returned if the corresponding condition is true. The ELSE clause specifies the value to be returned if none of the conditions are true.

For example, to assign a label to customers based on their order amount, you can use the following query:

SELECT customer_name,
       order_amount,
       CASE
           WHEN order_amount > 1000 THEN 'High Value'
           WHEN order_amount > 500 THEN 'Medium Value'
           ELSE 'Low Value'
       END AS label
FROM orders;

       CASE           WHEN order_amount > 1000 THEN ‘High Value’           WHEN order_amount > 500 THEN ‘Medium Value’           ELSE ‘Low Value’       END AS labelFROM orders; In this example, we use the CASE statement to assign a label to each customer based on their order amount. If the order amount is greater than 1000, we assign the label “High Value”. If the order amount is greater than 500, we assign the label “Medium Value”. Otherwise, we assign the label “Low Value”. You can use other logical operators such as =, <, >, <=, >=, <>, etc. with the CASE statement. 

How to use IF Else Statement in SQL Server?

The IF…ELSE statement in SQL Server is used to execute a block of code if a condition is true, and another block of code if the condition is false. Here’s an example of how to use it:

DECLARE @num INT = 10;

IF @num > 0
BEGIN
    PRINT 'The number is positive.';
END
ELSE
BEGIN
    PRINT 'The number is negative or zero.';
END

In this example, we declare a variable @num and assign it a value of 10. We then use the IF…ELSE statement to check if @num is greater than 0. If it is, we print the message “The number is positive.” If it is not, we print the message “The number is negative or zero.”

You can use other logical operators such as =, <, >, <=, >=, <>, etc. with the IF…ELSE statement.

Scroll to Top