Control Statements in T-SQL

Control statements are used to control execution flow within a T-SQL. Here I provides some example on control statements.

·         BEGIN-END:

Used to group a series of T-SQL statements in a single batch.
BEGIN-END can be nested.

If you are C#, C, or java programmer then you are aware about BEGIN-END like   {     } block.

DECLARE @val int;
BEGIN
SET @val=100
PRINT @val
END

Output:

100

•  IF-ELSE: Put the conditions on the execution of Transact-SQL statements.

DECLARE @sal int
SELECT @sal=salary from Person where pname like '%ABC%'
IF (@sal>10000)
PRINT 'Salary is greater than 10000'
ELSE
PRINT 'Salary is less than 10000'

·       WHILE loop:Simple example of while loop

DECLARE @cnt int
SET @cnt=0      --Initialization
WHILE(@cnt<5)   --Check condition
BEGIN
PRINT @cnt
SET @cnt=@cnt+1     --Increase counter by 1
END

Output:

0

1

2

3

4

·         CONTINUE & BREAK:

Continue restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored.

BREAK exits the innermost loop in a WHILE loop or an IF…ELSE statement inside a WHILE loop.

SET @cnt = 0          --Initialization
WHILE (@cnt < 10)     --Check Condition
BEGIN
     BEGIN
      PRINT @cnt            --Print Values
      SET @cnt = @cnt + 1   -- Increase counter by 1
     END
  IF(@cnt > 5)              --Check condition if true loop will break
  BREAK
  ELSE
  CONTINUE                  --False then loop continue
  END
DECLARE @cnt int

Output:

0

1

2

3

4

5

After CONTINUE statement all statement are ignoured.

DECLARE @cnt int
SET @cnt = 1
WHILE (@cnt <=5)
BEGIN
PRINT @cnt
SET @cnt = @cnt + 1
CONTINUE;
IF @cnt = 4 –-it is never executed
BREAK;
END

      Output:

1

2

3

4

5

·         CASE: It works as switch in C#,C,C++. It is used to evaluate several conditions and return a single value for each condition.

DECLARE @category CHAR
SET @category = 'S'
SELECT
CASE @category
WHEN 'F' THEN 'FACULTY'
WHEN 'S' THEN 'STUDENT'
WHEN 'D' THEN 'DIRECTOR'
WHEN 'L' THEN 'LAB ASSISTANT'
ELSE 'ADMIN'
END

Output:

STUDENT

·         GOTO: The Transact-SQL statement or statements that follow GOTO are skipped and processing continues at the label. 

DECLARE @cnt int
SET @cnt=0       --Initialization
WHILE(@cnt<10)   --Check condition
BEGIN
  
   IF(@cnt>=5)    --if counter greater than 5 control goto label1
               BEGIN
               PRINT @cnt
               GOTO Lable1
               END
    ELSE
               BEGIN
               PRINT @cnt
               SET @cnt=@cnt+1     --Increase counter by 1
               END
END
Lable1:
PRINT 'Value is larger than 5.'

Output:

0

1

2

3

4

5

Value is larger than 5.

Prev---> Operators in T-SQL                                                                 Next--->Database Creation using T-SQL