False results being returned from stored procedure?

Question

I've written a stored procedure in Microsoft SQL Server that should import results from 4Matrix and insert them into the Results table on MyPortal. It does import results, however, some of them are incorrect.

Here's the code for the stored procedure ImportResults:

USE [MyPortal]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      CURRICULUM\r.richards
-- Create date: 24/10/2017
-- Description: Imports Selected Subject Results From 4Matrix into MyPortal
-- =============================================
ALTER PROCEDURE [dbo].[ImportResults]
    @SubjectID int,
    @ResultSet varchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @StudentID int
    DECLARE @Upper int = (SELECT COUNT(*) FROM Students)
    DECLARE @ResultSetID int
    DECLARE @ResultValue NVARCHAR(50)
    DECLARE @Pointer int = 1

    WHILE @Pointer <= @Upper
    BEGIN
        SET @StudentID = (SELECT studentID 
                          FROM 
                              (SELECT
                                   ROW_NUMBER() OVER (ORDER BY Students.studentLastName) AS rownumber,
                                   studentID
                               FROM 
                                   Students) AS foo
                          WHERE rownumber = @Pointer)
        SET @ResultSetID = (SELECT rsID 
                            FROM ResultSets 
                            WHERE (rsName = @ResultSet))

        EXEC Get4MResult @StudentID, @SubjectID, @ResultSet, @Result = @ResultValue OUTPUT

        INSERT INTO Results(resultSet, resultStudent, resultSubject, resultValue)
        VALUES(@ResultSetID, @StudentID, @SubjectID, @ResultValue)

        SET @Pointer = @Pointer + 1
    END
END

The code part for the procedure 'Get4MResult' is as follows (in case it's this procedure causing the issue):

USE [MyPortal]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      CURRICULUM\r.richards
-- Create date: 24/10/2017
-- Description: Obtains Selected Result from 4Matrix Interface
-- =============================================
ALTER PROCEDURE [dbo].[Get4MResult]
    @StudentID int,
    @SubjectID int,
    @ResultSet varchar(50),
    @Result nvarchar(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @KeyStage INT
    DECLARE @4MPupilID INT
    DECLARE @4MSubjectID INT
    DECLARE @4MSeriesID INT

    DECLARE @YearGroup VARCHAR(50) = (SELECT MyPortal.dbo.Students.studentYear 
                                      FROM MyPortal.dbo.Students 
                                      WHERE(Students.studentID = @StudentID))

    IF(@YearGroup = 'Year 10' OR @YearGroup = 'Year 11')
        SET @KeyStage = 4
    ELSE 
        SET @KeyStage = 3

    SET @4MPupilID = (SELECT FOUR.dbo.Pupils.PupilID 
                      FROM FOUR.dbo.Pupils 
                      WHERE MIS = @StudentID)

    IF(@KeyStage = 4)
        SET @4MSubjectID = (SELECT MyPortal.dbo.Subjects.subjectQsiKs4 
                            FROM MyPortal.dbo.Subjects 
                            WHERE (MyPortal.dbo.Subjects.subjectID = @SubjectID))
    ELSE 
        SET @4MSubjectID = (SELECT MyPortal.dbo.Subjects.subjectQsiKs3 
                            FROM MyPortal.dbo.Subjects  
                            WHERE (MyPortal.dbo.Subjects.subjectID = @SubjectID))

    SET @4MSeriesID = (SELECT FOUR.dbo.Series.SeriesID 
                       FROM FOUR.dbo.Series 
                       WHERE (SeriesName = @YearGroup+' - '+@ResultSet))

    SELECT @Result = Result 
    FROM FOUR.dbo.Results 
    WHERE (PupilID = @4MPupilID 
      AND SubjectID = @4MSubjectID 
      AND SeriesID = @4MSeriesID)
END

In theory, only students with 4M IDs (from the Students Table) should have results. Only KS3 and KS4 students have 4Matrix IDs, so there should only be around 400 results being entered into the results table when I run it.

When I run the script ImportResults 1,'Spring 2017', it does import results like below:

However, instead of the ~400 results I was expecting, there's 1080 result entries. They're not duplicates, but instead I found that some students have results that don't exist and I'm not sure where the script has gone wrong to cause this. Look here:

This student (4292) supposedly has an 'A*' in this subject. But, upon further inspection, I found that this student is not in KS3 or KS4, and so does not have a 4Matrix ID. So I'm not sure where the 'A*' has come from.

A result should not have been imported at all for this student.

I've gone through the code for both parts, and can't see where the error is occurring?

Which "some" are incorrect? What sample data have you used to check the results? What should the results be? IOW, more details allow for better help. — SezMe, Oct 24 '17 at 02:31
@SezMe apologies for my brief description. I have now elaborated on the issue above. Thanks. — Rowan Richards, Oct 24 '17 at 16:02

Rowan Richards · Accepted Answer · 2017-10-25T00:39:25.447

I managed to solve the problem:

The issue seemed to be in the original ImportResults procedure, where even if students did not have a 4Matrix ID, they were still being passed through with an ID of either 0 or NULL, which was returning the A* value.

I corrected the procedure as follows:

DECLARE @StudentID int
DECLARE @Upper int = (SELECT COUNT(*) FROM Students)
DECLARE @ResultSetID int
DECLARE @ResultValue NVARCHAR(50)
DECLARE @Pointer int = 1
DECLARE @4MatrixID int
WHILE @Pointer <= @Upper
  BEGIN
    SET @StudentID =(SELECT studentID FROM (
                                             SELECT
                                               ROW_NUMBER() OVER (ORDER BY 
Students.studentLastName) AS rownumber,
                                               studentID
                                             FROM Students
                                           ) AS foo
    WHERE rownumber = @Pointer)
    SET @4MatrixID = (SELECT student4mID FROM Students WHERE(studentID = 
@StudentID))


    IF(@4MatrixID IS NULL) <===== ###'ADDED THIS TO ELIMINATE STUDENTS WITHOUT IDs'###



      GOTO nextStudent <===== ###'SENDS TO THE END OF THE SCRIPT' [1]###




    SET @ResultSetID = (SELECT rsID FROM ResultSets WHERE(rsName = 
@ResultSet))
    EXEC Get4MResult @StudentID,@SubjectID,@ResultSet,@Result = @ResultValue 
OUTPUT
INSERT INTO Results(resultSet, resultStudent, resultSubject, resultValue)
  VALUES(@ResultSetID,@StudentID,@SubjectID,@ResultValue)




    nextStudent: <===[1]= ###'GETS PUSHED HERE AND THEREFORE BYPASSES THE RESULT RETRIEVAL'###
    SET @Pointer = @Pointer + 1
  END

It meant having to use the dreaded GOTO statement, but it works as it should now! :)

False results being returned from stored procedure?

1 Answers1