/* Using While FOR conditional flow instead of IF */

/* This is not something I would do usually.
I dont break rules unless absolutely justified. (Though, I do love to do so.)
So was the reason here.

I had to execute the below complicated logic in somewhat better and easy manner.
The task I had to acheive was to import data into a table and append it if @append is 1.
Else truncate the table and populate the fresh data. But I had to keep history before doing so.
So I must populate the history table only if @append is 0 and @keepHistory is 1 and then truncate the original table.
If @append and @keepHistory were both 0 I must not keep a history but still truncate the table.
This can be acheived with If block as shown below.
( Pls note I have not used actual tables to protect client Information. But I still would like to present the flow)
*/


DECLARE @append BIT
DECLARE @successful BIT
DECLARE @keepHistory BIT
DECLARE @err INT

IF @append = 0
BEGIN
........................Set @successful = 1;
........................IF @keepHistory = 1
........................BEGIN
...............................Insert into TblHistory
..............................SELECT * FROM tablename

..............................SET @err = @@error;

..............................IF ( @err <> 0 )
..............................BEGIN
............................................................ --ON ERROR do not truncate table.
................................... SET @successful = 0;
.............................. END
........................END
/* if successful truncate the table */
........................IF @successful = 1;
........................ BEGIN
.............................. truncate table tablename;
........................ END
END

/* The above code looks cumbersome and complicated to me.
And this might invite some bugs if someone tries to change the code after several days.
To avoid this I needed some structure similar to a Case statement or BREAK in Programming Langauges like JAVA, C++ etc.
I devised similar logic with WHILE block and BREAK statements in SQL.
Please suggest if there is a better or even if there is any other way to acheive this that you can think of.
I would be more than delighted to know it.

*/


---

DECLARE @append BIT
DECLARE @successful BIT
DECLARE @keepHistory BIT
DECLARE @err INT

WHILE @append = 0
BEGIN
........IF @keepHistory = 1
........BEGIN
................ Insert into TblHistory
................ SELECT * FROM tablename

................ SET @err = @@error;

................ IF ( @err <> 0 )
................ BEGIN
........................ SET @successful = 0;
........................BREAK; -- terminate without truncating the table.
................ END
........ END

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

/* With the use of while block and break statements I had to use less number of variables for acheiving the flow.
I have still used @successful even though it was unneccessary because I need it in the rest of the flow after the while block.
In this logic the table would not be truncated if there was an error while transfering data to history.
It is somewhat similar to returning from a function/procedure in SQl
or other programming langauges without executing some part of code on error.
Yes and I try to avoid all the GOTO statements for all the obvious reasons.
Also please note that commenting or removing the last break statement will result in an infinite while loop.
I hope this was helpful to you.
All suggestions are warmly welcome.
*/