/** Use WHILE and BREAK statements alongwith IF blocks to form flow similar to Switch Statements in Procedures and Functions **/

/* With successful use of while block and break statements,
I came up with an thought of acheving a flow similar to Switch Case statements, from programming langauge, in SQL.
Yes there is a case statement that we can use for Selecting, updating etc. But we cannot use the same for builing a business logic.
That is we cannot use a Switch or Case block that channels the flow of a business logic in Procedures, functions or mere scripts.
Hush!!! its hard to explain for me, but please try to imagine that I want to Select 'A' and then update table t1 if a @i is 1.
And Select 'B' and then delete table t3 if @i is 5. This, as far as I am aware, we cannot acheive with a single case Block in SQL.
Thus the following code.
*/



Declare @caseFlag Varchar(20)

SET @caseFlag = 'A'

WHILE 1 = 1
BEGIN

.....IF @caseFlag = 'A'
.....BEGIN
..........Print ' GOT A '
........../* Some other logic */
..........BREAK;
..... END

.....IF @caseFlag = 'B'
.....BEGIN
..........Print ' GOT B'
........../* Some other logic */
..........BREAK;
..... END

.....PRINT 'Got else'

.....BREAK ; -- REMOVING OR COMMENTING THIS BREAK WILL CAUSE AN INFINITE . .....WHILE LOOP.
END
/* I have use a true condition just to enter the block.
I check for conditions with the help of if statements.
Then break fom the block after the objective is acheived.
Pls note that if the last Break Statement is removed or commented, it would result in an infinite while loop.
One more advantage of this code would be that we can use any expression that evaluate to TRUE OR FALSE as Cases.
*/
---


/*
After testing the code above I just thought,
it would be great if could use it just like a switch statements that we use in Programming langauges.
Then I realised that I would need the flow to fall down from one case to another if I dont use a Break statement.
To acheive this, I have commented out the BREAK statements in the IF blocks and added the exepression to the blocks below it.
This would allow the flow to fall through the cases just as in Switch statements in programming languages.

*/
Declare @caseFlag Varchar(20)

SET @caseFlag = 'A'

WHILE 1 = 1
BEGIN

.....IF @caseFlag = 'A'
.....BEGIN
..........Print ' GOT A '
........../* Some other logic */
..........-- BREAK;
.....END

.....IF @caseFlag = 'B' OR @caseFlag = 'A'
.....BEGIN
..........Print ' GOT B'
.......... /* Some other logic */
..........-- BREAK;
.....END

.....PRINT 'Got else'

.....BREAK ; -- REMOVING OR COMMENTING THIS BREAK WILL CAUSE AN INFINITE . .....WHILE LOOP.
END

/* This just a basic idea and can be twisted and turned to acheive more custom flows.
May be something like Falling through case 'A' but not going to 'B' and then still go through a case 'C'
that could be introduced. I can think of many more such examples but I sure you got the basic idea and can play more with it.
Please do let me know if this blog helped you or in what way I could improve it.
Please do suggest any improvement or any other method you can think of to acheive this.
All suggestions are warmly welcome.
*/