How do I loop through the results of a SELECT statement in SQL? My SELECT statement will return just 1 column but n results.
I have created a fictional scenario below complete with the Pseudo code of what I'm trying to do.
Scenario:
Students are registering for their classes. They submit a form with multiple course selections (ie. select 3 different courses at once). When they submit their registration I need to ensure there is still room left int the courses they have selected (note I will do a similar check before presenting them with course selection UI but I need to verify afterwards in case somebody else has gone in and swipped up the remaining spots).
Pseudo Code:
DECLARE @StudentId = 1
DECLARE @Capacity = 20
-- Classes will be the result of a Select statement which returns a list of ints
@Classes = SELECT classId FROM Student.CourseSelections
WHERE Student.CourseSelections = @StudentId
BEGIN TRANSACTION
DECLARE @ClassId int
foreach (@classId in @Classes)
{
SET @SeatsTaken = fnSeatsTaken @classId
if (@SeatsTaken > @Capacity)
{
ROLLBACK; -- I'll revert all their selections up to this point
RETURN -1;
}
else
{
-- set some flag so that this student is confirmed for the class
}
}
COMMIT
RETURN 0
My real problem is a similar "ticketing" problem. So if this approach seems very wrong please feel free to recommend something more practical.
EDIT:
Attempting to implement the solution below. At this point it doesn't work. Always returns "reserved".
DECLARE @Students TABLE
(
StudentId int
,StudentName nvarchar(max)
)
INSERT INTO @Students
(StudentId ,StudentName)
VALUES
(1, 'John Smith')
,(2, 'Jane Doe')
,(3, 'Jack Johnson')
,(4, 'Billy Preston')
-- Courses
DECLARE @Courses TABLE
(
CourseId int
,Capacity int
,CourseName nvarchar(max)
)
INSERT INTO @Courses
(CourseId, Capacity, CourseName)
VALUES
(1, 2, 'English Literature'),
(2, 10, 'Physical Education'),
(3, 2, 'Photography')
-- Linking Table
DECLARE @Courses_Students TABLE
(
Course_Student_Id int
,CourseId int
,StudentId int
)
INSERT INTO @Courses_Students
(Course_Student_Id, StudentId, CourseId)
VALUES
(1, 1, 1),
(2, 1, 3),
(3, 2, 1),
(4, 2, 2),
(5, 3, 2),
(6, 4, 1),
(7, 4, 2)
SELECT Students.StudentName, Courses.CourseName FROM @Students Students INNER JOIN
@Courses_Students Courses_Students ON Courses_Students.StudentId = Students.StudentId INNER JOIN
@Courses Courses ON Courses.CourseId = Courses_Students.CourseId
DECLARE @StudentId int = 4
-- Ideally the Capacity would be database driven
-- ie. come from the Courses.Capcity.
-- But I didn't want to complicate the HAVING statement since it doesn't seem to work already.
DECLARE @Capacity int = 1
IF EXISTS (Select *
FROM
@Courses Courses INNER JOIN
@Courses_Students Courses_Students ON Courses_Students.CourseId = Courses.CourseId
WHERE
Courses_Students.StudentId = @StudentId
GROUP BY
Courses.CourseId
HAVING
COUNT(*) > @Capacity)
BEGIN
SELECT 'full' as Status
END
ELSE BEGIN
SELECT 'reserved' as Status
END