Need to write a Stored Procedure

,
1

Hi ,

I need to create a Stored Procedure to Assign the students to Sections.
I have two table
1.STudent table (In this i have students details and (StudentID) is Primarykey
2.AssignStudent table.(In this table i have two Foreign Key columns (StudentID and SectionId)

In the Image If i click the forword symbol students need to get assigned
If i click the Backward symbol they need to get delete from AssignStudents Table.

How to do this ?

2

You should create two more tables, one for classes and one for sections. Or perhaps one table would be sufficient. If you go with the one-table option, create it with ClassId and SectionId as primary key.

Now you have three tables. When a student needs to be assigned to a class, insert a row in the AssignStudents table with the student Id, ClassId, and SectionId. When you want to remove a student from a class, delete the corresponding row from the AssignStudents table.

I would rename the AssignStudents table to StudentAssignments or StudentClassLink or something like that (as a noun rather than a verb).

Give that a try and if you need more help, reply with the code that you have, even if it does not work as you want it to.

1 Like
3

Hi James,

Thank you for the reply.

I have 4 tables now.

  1. Academy Students Tables with Primary Key as StudentId
  2. Academy Class Table with Primary Key as ClassId
  3. Academy Section Table with Primary Key as SectionId
  4. StudentsClassLink Table with FK as StudentId ,ClassId and SectionID.

So now In Academy Students Tables i have 10 Students and out of 10 i assigned 5 students.and Remaining 5 students only must show in List of STudents in above page.

SO what will be the code to show only the Unassigned students in the list of students ,.
Please help me.

Waiting for your reply.

4

What have you tried so far?

5

Hi Grasz,

Thank you for your reply.

iam trying

SELECT * from AcademyStudents
RIGHT JOIN SegmentStudents ON AcademyStudents.Guid = SegmentStudents.StudentId
WHERE (AcademyStudents.Guid = SegmentStudents.StudentId )

6

I would think you need something like this, where you are picking up all the students with a NOT EXISTS condition to eliminate the students who are already assigned to the selected class (@displayedClassId) and selected section (@displayedSectionId)

SELECT  *
FROM    AcademyStudents a
WHERE   NOT EXISTS 
		( SELECT *
          FROM   StudentsClassLink s
          WHERE  s.studentId = a.StudentId
                AND s.ClassId = @displayedClassId
                AND s.SectionId = @displayedSectionId
        )
1 Like
7

Thank you James !!