Question
Answer and Explanation
The CASE statement in Microsoft SQL Server stored procedures allows you to implement conditional logic. There are two main forms: simple CASE and searched CASE.
1. Simple CASE Statement:
The simple CASE statement compares an expression to a set of simple expressions to determine the result.
Syntax:
CASE input_expression
WHEN when_expression_1 THEN result_expression_1
WHEN when_expression_2 THEN result_expression_2
...
ELSE else_result_expression
END
Example:
CREATE PROCEDURE GetOrderStatusDescription (@OrderStatus INT)
AS
BEGIN
SELECT
CASE @OrderStatus
WHEN 1 THEN 'Order Received'
WHEN 2 THEN 'Processing'
WHEN 3 THEN 'Shipped'
WHEN 4 THEN 'Delivered'
ELSE 'Unknown Status'
END AS OrderStatusDescription;
END;
GO
2. Searched CASE Statement:
The searched CASE statement evaluates a list of Boolean expressions to determine the result.
Syntax:
CASE
WHEN boolean_expression_1 THEN result_expression_1
WHEN boolean_expression_2 THEN result_expression_2
...
ELSE else_result_expression
END
Example:
CREATE PROCEDURE GetCustomerSegment (@OrderTotal DECIMAL(10, 2))
AS
BEGIN
SELECT
CASE
WHEN @OrderTotal < 50 THEN 'Low Value Customer'
WHEN @OrderTotal >= 50 AND @OrderTotal < 100 THEN 'Medium Value Customer'
WHEN @OrderTotal >= 100 THEN 'High Value Customer'
ELSE 'Unknown Customer'
END AS CustomerSegment;
END;
GO
Important Considerations:
- The ELSE part is optional. If omitted and no WHEN condition is met, the CASE statement returns NULL.
- The CASE statement can be used wherever a valid expression can be used, such as in SELECT statements, WHERE clauses, and other parts of SQL queries.
- Use appropriate data types for expressions and results to avoid unexpected type conversion issues.
These examples should provide a clear understanding of how to implement CASE statements correctly within Microsoft SQL Server stored procedures.