SQL SERVER | Conditional Statements

In SQL Server, conditional logic can be implemented using various constructs within T-SQL (Transact-SQL, which is SQL Server's extension to SQL). Here's a breakdown of conditional statements available in T-SQL:

1. IF...ELSE

This is the most common conditional statement in T-SQL.

Syntax:

IF condition
BEGIN
    -- statements to execute if condition is true
END
ELSE
BEGIN
    -- statements to execute if condition is false
END

Example:

DECLARE @value INT = 10;

IF @value > 5
BEGIN
    PRINT 'Value is greater than 5';
END
ELSE
BEGIN
    PRINT 'Value is 5 or less';
END

2. CASE

The CASE statement is used for conditional logic within a SQL query, typically in the SELECT, UPDATE, or DELETE statement.

Syntax (Simple CASE expression):

CASE input_expression 
    WHEN when_expression THEN result_expression 
    [ ...n ]
    [ ELSE else_result_expression ]
END

Example:

SELECT name,
       CASE department
           WHEN 'Sales' THEN 'Sales Department'
           WHEN 'HR' THEN 'Human Resources'
           ELSE 'Other Departments'
       END AS DepartmentName
FROM employees;

Syntax (Searched CASE expression):

CASE 
    WHEN boolean_expression THEN result_expression 
    [ ...n ]
    [ ELSE else_result_expression ]
END

Example:

SELECT salary,
       CASE 
           WHEN salary > 50000 THEN 'High'
           WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryRange
FROM employees;

3. CHOOSE and IIF

CHOOSE and IIF are functions introduced in SQL Server 2012 to provide more concise ways to perform conditional operations.

CHOOSE:

Returns the item at the specified index from a list of values.

Syntax:

CHOOSE ( index, val_1, val_2 [, val_n ] )

Example:

SELECT CHOOSE(2, 'First', 'Second', 'Third') AS Result;  -- Returns 'Second'

IIF:

Returns one of two values depending on whether the boolean expression evaluates to true or false.

Syntax:

IIF ( boolean_expression, true_value, false_value )

Example:

SELECT IIF(salary > 50000, 'High', 'Low') AS SalaryRange
FROM employees;

These constructs allow developers to integrate conditional logic into their SQL Server queries and stored procedures, adding flexibility and dynamic capabilities to data retrieval and modification operations.

Using CASE statement in SQL Server:

The CASE statement allows conditional logic within a SQL query.

SELECT column1,
       column2,
       CASE
          WHEN condition1 THEN 'Result1'
          WHEN condition2 THEN 'Result2'
          ELSE 'DefaultResult'
       END AS custom_column
FROM example_table;

Conditional statements in SQL Server stored procedures:

Stored procedures in SQL Server can include conditional logic using IF statements.

CREATE PROCEDURE example_procedure
AS
BEGIN
   IF condition1
   BEGIN
      -- Logic when condition1 is true
   END
   ELSE
   BEGIN
      -- Logic when condition1 is false
   END
END;

Handling NULL values with COALESCE in SQL Server:
- The COALESCE function returns the first non-null expression.
```
SELECT column1, column2, COALESCE(column3, 'DefaultValue') AS result_column
FROM example_table;
```
Using SQL Server IIF function for conditional logic:
- The IIF function provides a shorthand for a simple CASE statement.
```
SELECT column1, column2, IIF(condition, 'TrueResult', 'FalseResult') AS result_column
FROM example_table;
```

Implementing SWITCH in SQL Server queries:

The SWITCH function allows multiple conditions to be evaluated.

SELECT column1,
       column2,
       SWITCH(
          condition1, 'Result1',
          condition2, 'Result2',
          'DefaultResult'
       ) AS result_column
FROM example_table;

Nested IF-ELSE statements in SQL Server:

IF statements can be nested for more complex conditional logic.

IF condition1
BEGIN
   IF condition2
   BEGIN
      -- Logic for condition2 when condition1 is true
   END
   ELSE
   BEGIN
      -- Logic for condition2 when condition1 is false
   END
END
ELSE
BEGIN
   -- Logic for condition1 is false
END

Overview of SQL Server conditional operators:
- Conditional operators like AND, OR, and NOT can be used for complex conditions.
```
SELECT column1, column2
FROM example_table
WHERE condition1 AND condition2;
```

SQL Server CHOOSE function for conditional selection:

The CHOOSE function returns the value based on the specified index.

SELECT column1, column2, CHOOSE(index,
                               'Result1',
                               'Result2',
                               'Result3') AS result_column
FROM example_table;