Overview:
In this article I would like to give you an overview on “Control Flow Statements” in T-SQL. I will not get into very detail, as in technology world there is no END to anything. This article is mainly for beginers to learn the basics.
Introduction:
In our world we use IF … ELSE very frequently. The most obvious statement in our day-2-day life is “If I am technically strong I would ask for more salary else I would settle with what company is offering”. You can’t image a life or a code with out conditional statements.
Now coming back to the business in T-SQL also we have Control Flow Statements.
These are classified into two categories:
1) Conditional Statements
2) Iterative Constructs
Conditional Statements:
As the name says you are examining something.
There are mainly two types of conditional statements available.
1.1) IF… ELSE
1.2) CASE…WHEN
1.1) IF… ELSE
First let’s look at the syntax:
IF (condition)
BEGIN
--Do Something
END
ELSE IF (condition)
BEGIN
--Do Something
END
ELSE
BEGIN
--Do Something
END
If the condition specified with the IF statement evaluates to true, a block of
Code in the IF block is executed otherwise the control goes to the ELSE block and the statements which are in this block are executed.
There is no limit on no. of IF…ELSE conditions. Its completely depends on the complex logic that you have.
Now let’s look at the simple example.
DECLARE
@dept_code CHAR(2),
@dept_name VARCHAR2(30)
BEGIN
SELECT @dept_code = 'IT'
IF dept_code = 'IT'
@dept_name= 'Information Technology'
ELSE IF dept_code = 'HR'
@dept_name= 'Human Resource'
ELSE IF dept_code = 'AD'
@dept_name= 'Admin Department'
ELSE
@dept_name= 'Invalid Department'
END
In this piece of code what we are doing is selecting "IT" value into @dept_code T-SQL variable and in the IF condition block examining this value and displaying department name. In this case, we are assigning value to "IT", the first IF condition is evaluated and it goes to BEGIN block of the first IF condition and it assigns “Information Technology” to @dept_name variable.
1.2) CASE…WHEN
This is very similar to IF…ELSE. The Main advantage with the CASE is, it can be used with in SELECT statements to display conditional messages.
Let’s look at the syntax:
CASE (expression)
WHEN value1 THEN
-- Do Something
WHEN value2 THEN
-- Do Something
[ELSE]
-- Do Something
END
As you can see it is very similar to that IF…ELSE condition.
Now lets re-write above example IF…ELSE code with CASE…WHEN.
DECLARE
@dept_code CHAR(2),
@dept_name VARCHAR(30)
SELECT @dept_code= 'IT'
CASE @dept_code
WHEN 'IT' THEN 'Information Technology'
WHEN 'HR' THEN 'Human Resource'
WHEN 'AD' THEN 'Admin Department'
ELSE 'Invalid Department'
END
The CASE statement is more readable and very efficient. So, i would highly recommend using CASE…WHEN where ever applicable.
Iterative Constructs
These are basically provides an option to evaluate the same statements over and over unless the condition is satisfied. In T-SQL there is only type of Iterative constructs that is WHILE loop.
Let’s look at the syntax
While (condition)
BEGIN
- Do Something 1
- Do Something 2
- Do Something 3
End
Before each and every iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements are executed with in the BEGIN block, then control resumes at the top of the loop. If the condition is false then the loop is bypassed and control passes to the next set of statements which are in T-SQL block.
Let’s look at example
DECLARE
@MySequence NUMERIC
SELECT @MySequence = 1
WHILE @MySequence <100
BEGIN
SELECT @MySequence = @MySequence +1
END
Summary
Happy Coding….
|
No responses found. Be the first to respond and make money from revenue sharing program.
|