use master
/*
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
Insert Student values(4,'fPrasanna','ACTIVE')
Insert Student values(24,'a','ACTIVE')


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 Cursor to Loop through the Recor*********************/
Declare  @CurStudent cursor
Declare @Studentid	int
Declare @StudentName varchar(50)
Create table #tempTable(TempStudentid int, TempStudentName varchar(50) )
--Declaration of cursor 
set @CurStudent = cursor for 
select Studentid, StudentName from Student 
--Open cursor 
OPEN @CurStudent 
     --Fetch the First Record
     FETCH NEXT FROM @CurStudent INTO @Studentid, @StudentName
     WHILE @@FETCH_STATUS = 0
     BEGIN
        -- Do the operation of the Selected Row
	insert #tempTable values(@Studentid, @StudentName )
        -- Fetch the Next Record
	FETCH NEXT FROM @CurStudent INTO @Studentid, @StudentName
    End
select * from #tempTable
drop table #tempTable
--Close and Deallocate the cursor
close @CurStudent
Deallocate @CurStudent
go 


/******************** Usage of Nested Cursor *********************/
Declare  @CurStudent cursor
Declare  @CurAssignment cursor
Declare @Studentid	int
Declare @Assignmentid	int

set @CurStudent = cursor for 
select Studentid from Student 
OPEN @CurStudent 
	FETCH NEXT FROM @CurStudent INTO @Studentid
	WHILE @@FETCH_STATUS = 0
	BEGIN
		  -- Set Second Level Cursor
		  set @CurAssignment = cursor for 
		  select Assignmentid  from Assignment 
		  OPEN @CurAssignment 
		  FETCH NEXT FROM @CurAssignment INTO @Assignmentid
		  WHILE @@FETCH_STATUS = 0 -- Fetch status for Inner cursor
		  BEGIN
		  
		  if not exists (select StudentAssignmentid from StudentAssignment 
						 where Studentid = @Studentid and Assignmentid = @Assignmentid)
			Insert StudentAssignment ( Studentid, Assignmentid, AssignmentStatus)
			Select @Studentid, @Assignmentid, 'SUBMITTED'		
			
			--Next Record
			FETCH NEXT FROM @CurAssignment INTO @Assignmentid
		  End
		close @CurAssignment
		Deallocate @CurAssignment
		FETCH NEXT FROM @CurStudent INTO @Studentid
	End
close @CurStudent
Deallocate @CurStudent
go