IF ELSE Statement in SQL Server

IF ELSE Statement in SQL Server

Overview:
The IF ELSE statement in SQL Server allows you to control the flow of your queries based on specific conditions. It provides a way to execute different sets of SQL code depending on whether a condition evaluates to true or false. This feature is essential for writing conditional logic in your SQL Server scripts.

Syntax:

sql
IF condition
BEGIN
    -- SQL code to execute if the condition is true
END
ELSE
BEGIN
    -- SQL code to execute if the condition is false
END

Explanation:

1. The IF keyword is used to check a specific condition. If the condition evaluates to true, the SQL code inside the first BEGIN...END block will be executed. Otherwise, if the condition is false, the code inside the ELSE block will be executed.

2. The BEGIN and END keywords are used to delimit a block of SQL code. It is essential to use them, especially when multiple SQL statements need to be executed within the IF or ELSE block.

Example:

Let's consider an example where we want to check if a given product's quantity is below a threshold value. If it is, we want to set the product status as 'Out of Stock'; otherwise, we'll set it as 'In Stock'.

sql
-- Sample table: Products
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Quantity INT
);

-- Sample data
INSERT INTO Products (ProductID, ProductName, Quantity)
VALUES (1, 'Widget A', 10),
       (2, 'Widget B', 5),
       (3, 'Widget C', 20);

-- Check product quantity and update status
DECLARE @ProductID INT = 2;
DECLARE @ThresholdQuantity INT = 10;
DECLARE @ProductStatus NVARCHAR(20);

IF (SELECT Quantity FROM Products WHERE ProductID = @ProductID) <= @ThresholdQuantity
BEGIN
    SET @ProductStatus = 'Out of Stock';
END
ELSE
BEGIN
    SET @ProductStatus = 'In Stock';
END

-- Display the result
SELECT @ProductStatus AS ProductStatus;

Explanation:

In this example, we have a table Products with columns ProductID, ProductName, and Quantity. We want to check the quantity of a specific product (identified by @ProductID) against a threshold value (@ThresholdQuantity). If the product's quantity is less than or equal to the threshold, the @ProductStatus variable is set to 'Out of Stock'. Otherwise, it is set to 'In Stock'. Finally, we display the result using a SELECT statement.

Remember that the IF ELSE statement in SQL Server can be used within stored procedures, functions, or batches of SQL code. It helps in making your queries more dynamic and responsive to varying conditions.