/*
1. First Level Data Setup
Create table Student (Studentid int, StudentName varchar(50), StudentStatus varchar(50))
Insert Student values(1,'Vijay','ACTIVE')
Insert Student values(2,'Mahe','INACTIVE')
Insert Student values(3,'Prasanna','ACTIVE')
Select * from Student

Create table Assignment (Assignmentid int, AssignmentName varchar(50))
Insert Assignment values (101, 'English') 
Insert Assignment values (102, 'Tamil')
Insert Assignment values (103, 'Maths')
Insert Assignment values (104, 'Science')
Insert Assignment values (105, 'Social')

Create table StudentAssignment (StudentAssignmentid int identity(1,1), Studentid int, Assignmentid varchar(50), AssignmentStatus varchar(50))
*/


/*
3. Modification/Addition in the Master Table
Insert Student values(4,'Abdullah','Active')
Insert Assignment values (106, 'GK')
*/ 


/******************** Usage of single While Loop *********************/
declare @LoopCounter int 
declare @MaxCount int 
Create table #Student (id int, Studentid int, StudentName varchar(50), StudentStatus varchar(50))

	-- Resultset which has to be modified
	Insert #Student 
	Select 
			ROW_NUMBER() OVER (ORDER BY Studentid ASC) as rownum, Studentid,  StudentName, StudentStatus
	from Student  where StudentStatus = 'INACTIVE'
	
	set @LoopCounter  =1
	Select @MaxCount = COUNT(1) from #Student

	WHILE (@LoopCounter  <=  @MaxCount)
	BEGIN
			-- Action to be taken on each record of the Result
			if exists ( Select Student.Studentid from Student Student, #Student TempStud 
						where Student.Studentid = TempStud.Studentid and id =@LoopCounter
						)
			Begin 
						Update Student set StudentStatus ='ACTIVE' from  #Student TempStud  where
						Student.Studentid = TempStud.Studentid and id =@LoopCounter
			End
	-- Loop incrementor
	Set @LoopCounter  = @LoopCounter  +1
	End
drop table #Student




/******************** Usage of Nested While Loop *********************/
declare @iLoopCounter int 
declare @jLoopCounter int 
declare @Table1MaxCount int 
declare @Table2MaxCount int 

Create table #Student (id int, Studentid int, StudentName varchar(50), StudentStatus varchar(50))
Create table #Assignment (id int, Assignmentid int, AssignmentName varchar(50))

Insert #Student 
	Select 
			ROW_NUMBER() OVER (ORDER BY Studentid ASC) as rownum, Studentid,  StudentName, StudentStatus
	from Student  
 
Insert #Assignment 
	Select 
			ROW_NUMBER() OVER (ORDER BY Assignmentid ASC) as rownum, Assignmentid,  AssignmentName
	from Assignment
	

set @iLoopCounter =1
set @jLoopCounter =1
	
Select @Table1MaxCount = COUNT(1) from 	#Student
Select @Table2MaxCount = COUNT(1) from 	#Assignment

	WHILE (@iLoopCounter <=  @Table1MaxCount)
	BEGIN
			set @jLoopCounter =1
			WHILE (@jLoopCounter <=  @Table2MaxCount)
			BEGIN
					if not exists (select StudentAssignmentid from StudentAssignment StudAssign,  #Student Stud, #Assignment Assign
			 					   where StudAssign.Studentid = Stud.Studentid and StudAssign.Assignmentid = Assign.Assignmentid 
			 					   and Stud.id= @iLoopCounter and Assign.id= @jLoopCounter)
					Begin 
							Insert StudentAssignment ( Studentid, Assignmentid, AssignmentStatus)
							Select 
									(Select Studentid from #Student where  #Student.id=@iLoopCounter),
									(Select Assignmentid from #Assignment where  #Assignment.id=@jLoopCounter), 	
									'SUBMITTED'				
					End
					Set @jLoopCounter = @jLoopCounter +1		
			End
			Set @iLoopCounter = @iLoopCounter +1
	End
drop table #Student
drop table #Assignment
go