SQL Server Case

SQL server "Case" is used when a list of condition are to be checked with a list of value ,base on checking output should be return. There is a provision of else statement in case statement , when no condition match , else condition satisfy, You can define return value when no condition match in the else part.When no else part is there , the query return null. A case statement execute in order from top to bottom.

1)Here is the syntax of Case Statement.

CASE expression

WHEN value1 THEN return1

WHEN value1 THEN return1

...

...

WHEN value_n THEN return_n

ELSE return_n

END

2) Here is the example of Case.

SELECT 

         [Member_Category]= 

      CASE member_id

                         WHEN 1 THEN 'Member 1'

                         WHEN 2 THEN 'Member 3'

                         ELSE 'Other Member'

       END

             FROM tbl_members;

Output :
Member 1
Member 3
Other Member
...
...
...
...
 
You can use  Case in select statement ,you can use nested case statement in your query.You can write sub query in the output statement also.Here is the example.

3)Here is the example of Nested Case Statement.
 

SELECT [Member_Category]=CASE member_id

                    WHEN 1 THEN

                                   CASE gender_id

                                            WHEN 1 THEN 'Male:'+'Member 1'

                                            WHEN 2 THEN 'FeMale:'+'Member 2'

                                            ELSE 'Other Member'

                                      END

                                      WHEN 2 THEN 'Member 2'

                                      ELSE 'Other Member'

            END

FROM tbl_members;

4)You can write sub query in the output statement also.Here is the example.

SELECT 

     [Member_Category]=CASE a.member_id

                                WHEN 1 THEN (SELECT last_name FROM tbl_member WHERE member_id=a.member_id)

                                              ELSE 'Other Member'

                       END

             FROM tbl_member a;

You can you case statement in stored procedure ,functions,triggers ect.You can use case statement in where statement also.Below is the example of case statement use in where.

DECLARE @status_id INT=1

SELECT * FROM tbl_member a

               WHERE (CASE WHEN @status_id > 0 THEN a.member_id ELSE 1 END) =

(CASE WHEN @status_id > 0 THEN @status_id ELSE 1 END)

 

SQL Server Loop

SQL server hast only "while loop" concept ,there is no concept of "for loop" . A loop statement execute repeatedly until particular statement or condition reach.It is called "exit condition" or "exit point". You can specify the "exit condition" during the declaration of loop or you can specify the "exit condition" within the while condition.If you write "exit condition" within the while condition ,
you have to write "break" statement to break the loop."break" statement , break the loop , force to end the repetition work.

IF there is no "exit condition" or "exit condition" does not satisfy,the loop do repeat unending ,it is called Infinite loop.


1)Here is the Syntax of  "While Loop"

DECLARE @myValue1 INT = 0;

DECLARE @myValue2 INT = 10;

WHILE @myValue1 < @myValue2

BEGIN

{

    //Statement

    SET @myValue1 = @myValue1 + 1;

}

END;

2)Here is the example  of "While Loop"

DECLARE @myValue1 INT = 0;

DECLARE @myValue2 INT = 10;

WHILE @myValue1 < @myValue2

BEGIN

        PRINT 'SL:'+CONVERT(VARCHAR,@myValue1);

        SET @myValue1 = @myValue1 + 1;

END;

Output :

SL:0

SL:1

SL:2

SL:3

SL:4

SL:5

SL:6

SL:7

SL:8

SL:9

3)Here is the example  of "While Loop" with "break" statement

DECLARE @myValue1 INT = 0;

DECLARE @myValue2 INT = 10;

WHILE (1 = 1)

BEGIN

       PRINT 'SL:'+CONVERT(VARCHAR,@myValue1);

       IF (@myValue1 > @myValue2)

            BREAK;

            SET @myValue1 = @myValue1 + 1;  

END;

Output :
SL:0
SL:1
SL:2
SL:3
SL:4
SL:5
SL:6
SL:7
SL:8
SL:9
SL:10
SL:11

4)Here is the example of Nested "While Loop"

DECLARE @myValue1 INT = 0;

DECLARE @myValue2 INT = 5;

DECLARE @myValue3 INT = 0;

WHILE (@myValue3<5)

    BEGIN

             PRINT '------'+CONVERT(VARCHAR,@myValue3)+'---------------';

                       WHILE (@myValue1 < @myValue2)

                       BEGIN

                                PRINT 'SL:'+CONVERT(VARCHAR,@myValue1);

                                SET @myValue1 = @myValue1 + 1;

                         END;

                  SET @myValue1=0

                  SET @myValue3 = @myValue3 + 1;

END;

5)Here is the example  of "While Loop" used in a function.

CREATE FUNCTION dbo.fn_myFunction()

RETURNS VARCHAR(50)

AS

BEGIN

          DECLARE @myValue1 INT = 0;

          DECLARE @myValue2 INT = 10;

         WHILE @myValue1 < @myValue2

        BEGIN

        SET @myValue1 = @myValue1 + 1;

        END;

                     RETURN CONVERT(VARCHAR,@myValue1 )

END

Execute function

SELECT dbo.fn_myFunction()

Output :

10

6)Infinite Loop

WHILE (1=1)

BEGIN

    PRINT 'Loop is executing';

END;

Output :
Loop is executing
Loop is executing
Loop is executing
..
..
..