CREATE TABLE messages (results VARCHAR2(60)); Write a SQL script that implements

Question

CREATE TABLE messages (results VARCHAR2(60)); Write a SQL script that implements a block that contains a for-loop which increments a counter from 1 to 10 and inserts the counter value into the results field of the messages table you just created. Within the loop create an if-else statement that will insert a null in place of the counter value if the counter value is equal to either 6 or 8. Run the script. when you perform select * from messages; the output should look like this: RESULTS 1 2 3 4 5 - 7 - 9 10

Explanation / Answer

In T-SQL the WHILE statement is the most commonly used way to execute a loop. Here is the basic syntax for a WHILE loop: WHILE Where a is any expression that equates to a true or false answer, and the is the desire code to be executed while the is true. Let's go through a real simple example. In this example I will increment a counter from 1 to 10 and display the counter each time through the WHILE loop. declare @counter int set @counter = 0 while @counter < 10 begin set @counter = @counter + 1 print 'The counter is ' + cast(@counter as char) end Here the code executes the WHILE statement as long as the @counter integer variable is less than 10, this is the Boolean expression of the WHILE loop. The @counter variable starts out at zero, and each time through the WHILE loop it is incremented by 1. The PRINT statement displays the value in the @counter variable each time through the WHILE loop. The output from this sample looks like this: The counter is 1 The counter is 2 The counter is 3 The counter is 4 The counter is 5 The counter is 6 The counter is 7 The counter is 8 The counter is 9 The counter is 10 As you can see, once the @counter variable reaches 10 the Boolean expression that is controlling the WHILE loop is no longer true, so the code within the while loop is no longer executed. Not only can you have a single while loop, but you can have WHILE loops inside WHILE loops. Or commonly know as nesting of WHILE loops. There are lots of different uses where nesting is valuable. I commonly use nesting of WHILE loops to generate test data. My next example will use the WHILE loop to generate test records for a PART table. A given PART record is uniquely identified by a Part_Id, and a Category_Id. For each Part_Id there are three different Category_Id's. Here is my example that generates 6 unique records for my PART table using a nested WHILE loop. declare @Part_Id int declare @Category_Id int declare @Desc varchar(50) create table PART (Part_Id int, Category_Id int, Description varchar(50)) set @Part_Id = 0 set @Category_Id = 0 while @Part_Id < 2 begin set @Part_Id = @Part_Id + 1 while @Category_Id < 3 begin set @Category_Id = @Category_Id + 1 set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) + ' Category_Id ' + cast(@Category_Id as char(1)) insert into PART values(@Part_Id, @Category_Id, @Desc ) end set @Category_Id = 0 end select * from PART drop table PART Here is the output from the SELECT statement at the bottom of this nested WHILE loop example. Part_Id Category_Id Description ----------- ----------- ----------------------------------------- 1 1 Part_Id is 1 Category_Id 1 1 2 Part_Id is 1 Category_Id 2 1 3 Part_Id is 1 Category_Id 3 2 1 Part_Id is 2 Category_Id 1 2 2 Part_Id is 2 Category_Id 2 2 3 Part_Id is 2 Category_Id 3 As you can see, by using a nested WHILE loop each combination of Part_Id and Category_Id is unique. The code within the first WHILE loop controlled the incrementing of the Part_Id, where as the second WHILE loop set the Category_Id to a different value each time through the loop. The code within the first while loop was executed only twice, but the code inside the second WHILE loop was executed 6 times. Thus giving me 6 sample PART records.