Control Statements in SQL

For example:

DECLARE @age INT = 25;

IF @age >= 18
  BEGIN
    PRINT 'You are an adult.'
  END
ELSE
  BEGIN
    PRINT 'You are a minor.'
  END

In this example, an IF...THEN...ELSE control statement is used to check if a person is an adult based on their age. If the age is greater than or equal to 18, a message stating "You are an adult" will be displayed. Otherwise, the message "You are a minor" will be shown.

Consider the following example:

DECLARE @score INT = 75;

IF @score >= 60
  BEGIN
    PRINT 'Passed';
  END
ELSE
  BEGIN
    PRINT 'Failed';
  END

Here, the IF...THEN...ELSE statement checks whether the @score variable is greater than or equal to 60. If the condition is true, it will print 'Passed', otherwise, it will print 'Failed'.

Here's an example of a simple CASE expression:

SELECT OrderID,
  CASE ShipRegion
    WHEN 'North America' THEN 'NA'
    WHEN 'South America' THEN 'SA'
    WHEN 'Europe' THEN 'EU'
    ELSE 'Other'
  END as Region
FROM Orders;

In this example, the CASE statement assigns a short code to the ShipRegion column of each row in the Orders table based on the region's name.

Here's an example of a WHILE loop:

DECLARE @counter INT = 1;

WHILE @counter <= 10
  BEGIN
    PRINT CONCAT('Number: ', @counter);
    SET @counter = @counter + 1;
  END

In this example, the WHILE loop prints the numbers 1 through 10. The loop continues to iterate as long as the value of @counter is less than or equal to 10.

Here's an example of using a FOR loop with a cursor:

DECLARE @ProductName NVARCHAR(50);
DECLARE product_cursor CURSOR FOR
  SELECT ProductName
  FROM Products
  WHERE CategoryID = 2;

OPEN product_cursor;

FETCH NEXT FROM product_cursor INTO @ProductName;

WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT @ProductName;
    FETCH NEXT FROM product_cursor INTO @ProductName;
  END

CLOSE product_cursor;
DEALLOCATE product_cursor;

In this example, the FOR loop iterates through the ProductName column of the Products table for all rows with a CategoryID of 2. The loop prints out each ProductName, using the cursor to manage the looping process.

Example 1: Calculate bonuses for employees based on their performance rating:

DECLARE @PerformanceRating INT = 5;
DECLARE @BonusAmount DECIMAL(7,2);

IF @PerformanceRating >= 4
  BEGIN
    SET @BonusAmount = 1000.00;
    PRINT 'Bonus amount: ' + CAST(@BonusAmount AS NVARCHAR) + ' for excellent performance.';
  END
ELSE
  BEGIN
    SET @BonusAmount = 500.00;
    PRINT 'Bonus amount: ' + CAST(@BonusAmount AS NVARCHAR) + ' for satisfactory performance.';
  END

In this example, the IF statement checks an employee's performance rating and calculates their bonus amount accordingly. With excellent performance (a rating of 4 or higher), the system will assign a bonus of 1000.00. Otherwise, the bonus will be 500.00.

Example 1: Calculate discounts for customers based on their membership status:

SELECT CustomerID, 
  MembershipStatus,
  TotalSpent,
  CASE MembershipStatus
    WHEN 'Gold' THEN TotalSpent * 0.10
    WHEN 'Silver' THEN TotalSpent * 0.05
    ELSE TotalSpent * 0.02
  END AS DiscountedAmount
FROM Customers;

This example demonstrates the use of a simple CASE expression. The membership status column from the Customers table is evaluated, and a discount percentage is applied based on the membership tier (Gold - 10%, Silver - 5%, others - 2%). The result is stored in the DiscountedAmount column.

Example 1: Create a table with a WHILE loop:

DECLARE @Sales TABLE (Month INT, Amount DECIMAL(7,2));
DECLARE @MonthCounter INT = 1;

WHILE @MonthCounter <= 12
  BEGIN
    INSERT INTO @Sales (Month, Amount) VALUES (@MonthCounter, RAND() * 10000);
    SET @MonthCounter = @MonthCounter + 1;
  END

In this example, a temporary table named @Sales with columns for Months and Amounts is created. A WHILE loop populates the table with dummy sales data for each month using the RAND() function. This is achieved by incrementally increasing the value of @MonthCounter until it reaches 12.

Example 2: Process records with a LOOP using a cursor:

DECLARE @ProductID INT;
DECLARE product_cursor CURSOR FOR
  SELECT ProductID
  FROM Products
  WHERE Discontinued = 0;

OPEN product_cursor;

FETCH NEXT FROM product_cursor INTO @ProductID;

WHILE @@FETCH_STATUS = 0
  BEGIN
    -- Perform operations on the @ProductID here (e.g., update, delete, etc.)
    FETCH NEXT FROM product_cursor INTO @ProductID;
  END

CLOSE product_cursor;
DEALLOCATE product_cursor;

In this example, a LOOP is created using a cursor named product_cursor to process all non-discontinued products in the Products table. The WHILE loop evaluates the @@FETCH_STATUS system variable, continuing to execute as long as there are more records to process. The loop terminates once all records have been processed.