October 3, 2011 at 1:53 pm
I'll start with the entire SP here and then document the problem below:
ALTER PROCEDURE [TEAMS].[MoveStudentFromOneSectionToAnotherSection]
@StudentID BIGINT,
@SchoolID BIGINT,
@OldSectionId BIGINT,
@NewSectionId BIGINT
AS
BEGIN
DECLARE @AddSegmentEnrollment BIT;
DECLARE @AddSectionEnrollment BIT;
DECLARE @Counter INT;
IF @NewSectionId <> 0
BEGIN
BEGIN TRANSACTION
--Find out the section from where student needs to drop exists or not?
SET @Counter = 0;
SELECT @Counter = COUNT(*)
FROM Section s
INNER JOIN Course c ON s.CourseId = c.CourseId
INNER JOIN CourseOffering co ON co.CourseId = c.CourseId
INNER JOIN SectionEnrollment se ON se.SectionId = s.SectionId
INNER JOIN Calendar cal ON cal.CalendarId = co.CalendarId
INNER JOIN School sch ON sch.CurrentCalendarId = cal.CalendarId
WHERE (s.SectionId = @OldSectionId)
AND (se.StudentId = @StudentId)
AND (sch.SchoolId = @SchoolID);
/*
IF EXISTS(SELECT 1
FROM Section s
INNER JOIN Course c ON s.CourseId = c.CourseId
INNER JOIN CourseOffering co ON co.CourseId = c.CourseId
INNER JOIN SectionEnrollment se ON se.SectionId = s.SectionId
INNER JOIN Calendar cal ON cal.CalendarId = co.CalendarId
INNER JOIN School sch ON sch.CurrentCalendarId = cal.CalendarId
WHERE (s.SectionId = @OldSectionId)
AND (se.StudentId = @StudentId)
AND (sch.SchoolId = @SchoolID))
*/
IF @Counter > 0
BEGIN
SET @AddSectionEnrollment = 0;
SET @AddSegmentEnrollment = 0;
--Drop student from existing sectionId
UPDATE sge
SET sge.DropDate = CONVERT(DATE, GETDATE())
FROM Segment sg
INNER JOIN SegmentEnrollment sge ON sge.SegmentId = sg.SegmentId
INNER JOIN Section s ON s.SectionId = sg.SectionId
WHERE (s.SectionId = @OldSectionId)
AND (sge.StudentId = @StudentID);
END;
--Find out the section from where student needs to add exists or not?
SET @Counter = 0;
SELECT @Counter = COUNT(*)
FROM Section
WHERE (SectionId = @NewSectionId);
/*
IF EXISTS(SELECT 1
FROM Section
WHERE (SectionId = @NewSectionId))
*/
IF @Counter > 0
BEGIN
-- Check to see if rescheduling to a previously dropped teacher
SET @Counter = 0;
SELECT @Counter = COUNT(*)
FROM SectionEnrollment se
WHERE (se.SectionId = @NewSectionId)
AND (se.StudentId = @StudentID);
/*
IF EXISTS(SELECT 1
FROM SectionEnrollment se
WHERE (se.SectionId = @NewSectionId)
AND (se.StudentId = @StudentID))
*/
IF @Counter > 0
BEGIN
SET @Counter = 0;
print @counter
print @newsectionid
print @studentid
SELECT @Counter = COUNT(*)
FROM Segment sg
INNER JOIN SegmentEnrollment sge ON sge.SegmentId = sg.SegmentId
WHERE (sg.SectionId = @NewSectionId)
AND (sge.StudentId = @StudentID);
print @counter
print @newsectionid
print @studentid
/*
IF EXISTS(SELECT 1
FROM Segment sg
INNER JOIN SegmentEnrollment sge ON sge.SegmentId = sg.SegmentId
WHERE (sg.SectionId = @NewSectionId)
AND (sge.StudentId = @StudentID))
*/
IF @Counter > 0
BEGIN
UPDATE sge
SET sge.AddDate = CONVERT(DATE, GETDATE()),
sge.DropDate = NULL
FROM Segment sg
INNER JOIN SegmentEnrollment sge ON sge.SegmentId = sg.SegmentId
INNER JOIN Section s ON s.SectionId = sg.SectionId
WHERE (s.SectionId = @NewSectionId)
AND (sge.StudentId = @StudentID);
END
ELSE
BEGIN
-- Add segmentEnrollment entries
SET @AddSegmentEnrollment = 1;
END;
END
ELSE
BEGIN
-- Add Sectionenrollment and segmentEnrollment entries
SET @AddSectionEnrollment = 1;
SET @AddSegmentEnrollment = 1;
END;
IF @AddSectionEnrollment = 1
BEGIN
INSERT INTO SectionEnrollment(StudentId,
SectionId,
IsNotGraded,
UniqueId,
IsPreAssigned,
ClickerId)
VALUES (@StudentID,
@NewSectionId,
0,
NEWID(),
0,
NULL);
END;
IF @AddSegmentEnrollment = 1
BEGIN
DECLARE @SectionEnrollmentId BIGINT = 0;
DECLARE @Calendar BIGINT = 0;
DECLARE @MasterScheduleId BIGINT = 0;
DECLARE @MeetingPatternOverrideId BIGINT = NULL;
SELECT TOP 1 @Calendar = sge.CalendarId,
@MeetingPatternOverrideId = sge.MeetingPatternOverrideId,
@MasterScheduleId = sge.MasterScheduleId
FROM SegmentEnrollment sge
INNER JOIN Segment sg ON sge.SegmentId = sg.SegmentId
WHERE (sg.SectionId = @NewSectionId);
IF @Calendar = 0 OR @MasterScheduleId = 0
BEGIN
SELECT @Calendar = CurrentCalendarId
FROM School s
WHERE (s.SchoolId = @SchoolID);
SELECT @MasterScheduleId = MasterScheduleId
FROM MasterSchedule m
WHERE (m.CalendarId = @Calendar);
END;
SELECT @SectionEnrollmentId = se.SectionEnrollmentId
FROM SectionEnrollment se
WHERE (se.SectionId = @NewSectionId)
AND (se.StudentId = @StudentID);
IF NOT EXISTS(SELECT 1
FROM Segment s
WHERE (s.SectionId = @NewSectionId))
BEGIN
DECLARE @Period VARCHAR(10);
SELECT @Period = MeetingPatternReportCode
FROM RptCourseSection s
WHERE (s.SectionId = @NewSectionId);
INSERT INTO Segment (SectionId,
SegmentNumber,
NStudents,
MeetingPatternId,
Meets,
StartTime,
Minutes,
DaysMeets,
LocalId)
SELECT @NewSectionId,
1,
0,
MeetingPatternId,
'',
0,
0,
'',
''
FROM MeetingPattern
WHERE (CalendarId = @Calendar)
AND (ShortCode = @Period)
AND (TermCodes = 'S1,S2')
END;
INSERT INTO SegmentEnrollment (SectionEnrollmentId,
StudentId,
SegmentId,
CalendarId,
MasterScheduleId,
MeetingPatternOverrideId,
AddDate,
DropDate,
UniqueId,
IsGradeExpected,
DropReason)
SELECT @SectionEnrollmentId AS SectionEnrollmentId,
@StudentId AS StudentId,
sg.SegmentId AS SegmentId,
@Calendar AS CalendarId,
@MasterScheduleId AS MasterScheduleId,
@MeetingPatternOverrideId AS MeetingPatternOverrideId,
CONVERT(DATE, GETDATE()) AS AddDate,
NULL AS DropDate,
NEWID() AS UniqueId,
1 AS IsGradeExpected,
NULL AS DropReason
FROM Segment sg
INNER JOIN MeetingPattern mp ON sg.MeetingPatternId = mp.MeetingPatternId
WHERE (sg.SectionId = @NewSectionId)
AND (mp.TermCodes = 'S1,S2');
END;
COMMIT TRANSACTION;
END
ELSE
BEGIN
--NewSection does not exists so rollback the work you have done so far.
ROLLBACK TRANSACTION;
END;
END;
END;
At line 93, the SP is supposed to get a count of records with the following statement:
SET @Counter = 0;
SELECT @Counter = COUNT(*)
FROM Segment sg
INNER JOIN SegmentEnrollment sge ON sge.SegmentId = sg.SegmentId
WHERE (sg.SectionId = @NewSectionId)
AND (sge.StudentId = @StudentID);
When I pass in a particular section for a student to be enrolled in, the count returned should be 0. In fact, when I run the following statement outside of the SP, it indeed returns a count of 0:
SELECT COUNT(*)
FROM Segment sg
INNER JOIN SegmentEnrollment sge ON sge.SegmentId = sg.SegmentId
WHERE (sg.SectionId = 212023)
AND (sge.StudentId = 62687)
I have verified that SegmentEnrollment does not have any records for the Section for this particular student.
You can see in the entire SP at the top that I added several print lines to see what the variables were set to at those points in time. When I run the SP (EXEC Pinnacle.TEAMS.MoveStudentFromOneSectionToAnotherSection 62687, 12, 0, 212023;), the output is:
0
212023
62687
2
212023
62687
(2 row(s) affected)
When I run a trace, it is falling into the UPDATE statement on line 114 which is incorrect. Since the count is supposed to be 0, it should be falling to the ELSE statement and setting @AddSegmentEnrollment = 1 so the rest of the SP will work correctly.
Why is @Counter getting set to 2? Where are the 2 rows affected coming from?
October 3, 2011 at 3:11 pm
@Counter means different things in different places, Jim. Not easy to follow. Try this instead:
SELECT @OldSectionFoundId = TOP 1 s.SectionId
FROM Section s
INNER JOIN Course c ON s.CourseId = c.CourseId
INNER JOIN CourseOffering co ON co.CourseId = c.CourseId
INNER JOIN SectionEnrollment se ON se.SectionId = s.SectionId
INNER JOIN Calendar cal ON cal.CalendarId = co.CalendarId
INNER JOIN School sch ON sch.CurrentCalendarId = cal.CalendarId
WHERE (s.SectionId = @OldSectionId)
AND (se.StudentId = @StudentId)
AND (sch.SchoolId = @SchoolID)
ORDER BY s.SectionId;
If @OldSectionFoundId is null after this statement is run, then the old section doesn't exist.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 3, 2011 at 3:22 pm
That isn't the point at which the problem is occurring. The @Counter and IF statement at that early point in the procedure is working as expected. It is further down in at line 93 that it is failing.
Also note that I usually code using IF EXISTS which is commented out in the code since that is failing as well. I switched to using the @Counter to see if I could find where this thing is going wrong.
October 3, 2011 at 3:44 pm
jim.powers (10/3/2011)
That isn't the point at which the problem is occurring. ...
Nope, but it sure will make it easier to find - this time and the next.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply