T-SQL, or Transact-SQL, is a procedural programming language used in Microsoft SQL Server. It provides several conditional statements that allow users to create decision-making processes based on specific conditions. These statements include IF...ELSE, CASE, and CHOOSE.
BEGIN
-- statement or block of statements executed if condition
is true
END
ELSE
BEGIN
-- statement or block of statements executed if condition
is false
END
In this syntax, the condition is a logical expression that evaluates to either true or false. If the condition is true, the statement or block of statements inside the BEGIN and END keywords will be executed. If the condition is false, the statement or block of statements inside the ELSE clause will be executed.
Let's take
a look at an example to better understand the usage of the IF-ELSE statement:
DECLARE @number int = 10
IF @number > 0
BEGIN
PRINT 'The number is
positive'
END
ELSE
BEGIN
PRINT 'The number is
negative or zero'
END
In this example, the condition is @number > 0, which is true since @number is 10. Therefore, the statement inside the BEGIN and END keywords after the IF keyword will be executed, which is to print "The number is positive".
You can
also use the IF-ELSE statement with multiple conditions by using the ELSE IF
clause. The syntax for the ELSE IF clause is as follows:
IF condition1
BEGIN
-- statement or block of statements executed if
condition1 is true
END
ELSE IF condition2
BEGIN
-- statement or block of statements executed if
condition2 is true
END
ELSE
BEGIN
-- statement or block of statements executed if all
conditions are false
END
Let's take
a look at an example that uses the ELSE IF clause:
DECLARE @number int = 0
IF @number > 0
BEGIN
PRINT 'The number is
positive'
END
ELSE IF @number < 0
BEGIN
PRINT 'The number is
negative'
END
ELSE
BEGIN
PRINT 'The number is
zero'
END
In this example, the condition @number > 0 is false since @number is 0. Therefore, the ELSE IF clause will be evaluated, and the condition @number < 0 will also be false. Finally, the ELSE clause will be executed, which prints "The number is zero"
CASE
Statement:
The CASE
statement is used to create multiple conditions in a single SQL statement. It
has two variations: the simple CASE and the searched CASE.
The simple
CASE statement has the following syntax:
CASE
expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE resultN
END
Here,
expression is the value that we want to evaluate. If it matches any of the
valueX, then the corresponding resultX is returned. If none of the values
match, then the ELSE block is executed.
For
example, consider a table product that contains the details of products and
their prices. We can use the CASE statement to categorize the products based on
their prices.
SELECT name, price,
CASE
WHEN price < 100 THEN 'Low Price'
WHEN price >= 100 AND price < 500 THEN 'Medium Price'
ELSE 'High Price'
END AS price_category
FROM product
Here, the
CASE statement evaluates the price of each product and categorizes it as Low
Price, Medium Price, or High Price.
The
searched CASE statement has a similar syntax but uses Boolean expressions
instead of values.
CASE
WHEN boolean_expression1 THEN result1
WHEN boolean_expression2 THEN result2
...
ELSE resultN
END
CHOOSE Statement:
The CHOOSE
statement is used to select a value from a list of expressions based on an
index. It has the following syntax:
CHOOSE (index, value1, value2, ..., valueN)
Here, index
is the position of the value that we want to select, and valueX is the list of
expressions from which we want to select.
For
example, consider the following query:
SELECT CHOOSE(2, 'apple', 'banana', 'orange')
Here, the
CHOOSE statement selects the value banana as it is in the second position in
the list of expressions.
WHILE
loop
In SQL
Server, the WHILE loop is a control flow statement that allows you to
repeatedly execute a block of statements as long as a specific condition is
true. It is a powerful feature of T-SQL that is commonly used in various
database applications. This blog will cover the usage of the WHILE loop in SQL
Server and how it can be used to write effective code.
The syntax
for the WHILE loop is as follows:
WHILE
condition
BEGIN
-- statement or block of statements executed as long as
condition is true
END
In this
syntax, the condition is a logical expression that evaluates to either true or
false. As long as the condition is true, the statement or block of statements
inside the BEGIN and END keywords will be executed repeatedly.
Let's take
a look at an example to better understand the usage of the WHILE loop:
DECLARE @count int = 1
WHILE @count <= 10
BEGIN
PRINT @count
SET @count = @count + 1
END
In this
example, the condition is @count <= 10, which is true since @count is
initially 1. Therefore, the statement inside the BEGIN and END keywords after
the WHILE keyword will be executed, which is to print the value of @count. The
value of @count is then incremented by 1 using the SET statement. The loop will
continue to execute as long as @count is less than or equal to 10.
You can
also use the WHILE loop with multiple conditions by using the logical operators
AND and OR. The syntax for using logical operators in the WHILE loop is as
follows:
WHILE
condition1 AND/OR condition2 AND/OR condition3
BEGIN
-- statement or block of statements executed as long as
all conditions are true
END
Let's take
a look at an example that uses logical operators in the WHILE loop:
DECLARE @count int = 1
DECLARE @flag bit = 1
WHILE @count <= 10 AND @flag = 1
BEGIN
PRINT @count
SET @count = @count + 1
IF @count = 6
SET @flag = 0
END
Comments
Post a Comment