Pages

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

No comments:

Post a Comment

Please leave a comment for this post