Question

Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following: Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE Outputs the numeric grade and the letter grade back to the user If the numeric grade is found, return 0, otherwise return 1 You muse use a cursor to loop through the GRADE_CONVERSION table to find the letter grade

Answer 1

create PROCEDURE [dbo].[ap_CalcGrade]
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,
@LetterVal char(2) output
AS

SET NOCOUNT ON;

SELECT @Numeric = Numeric_Grade from [Student].dbo.Grade
where STUDENT_ID = @studId AND
SECTION_ID = @secId AND
GRADE_TYPE_CODE = @grdTyCd AND
GRADE_CODE_OCCURRENCE = @grdCdOcc;

IF @@ROWCOUNT = 0 -- wrong parameters passed
RETURN 1; -- not found

Declare @CrsrVar Cursor LOCAL STATIC FAST_FORWARD

For
SELECT LETTER_GRADE
FROM GRADE_CONVERSION
WHERE
MAX_GRADE >= @Numeric AND MIN_GRADE <= @Numeric -- should return a single row cursor, but may return a few rows in case we have overlapping ranges

Open @CrsrVar

Fetch Next From @CrsrVar
Into @LetterVal

While (@@FETCH_STATUS = 0)
BEGIN

Fetch Next From @CrsrVar
Into @LetterVal -- will return the latest grade from that cursor

END

Close @CrsrVar
Deallocate @CrsrVar

IF @LetterVal IS NOT NULL
RETURN 0;-- found some data
ELSE
RETURN 1; -- not found letter grade in the GradeConversion
GO