September 18, 2015 at 12:26 pm
Hi, I'm having trouble with my SQL class assignment and would really appreciate some advice. I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Below is the stored procedure I've created:
ALTER 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
Declare @CrsrVar Cursor
Set @CrsrVar = Cursor For
SELECT LETTER_GRADE, NUMERIC_GRADE
FROM [STUDENT].[dbo].[GRADE], GRADE_CONVERSION
WHERE (STUDENT_ID = @studId AND
SECTION_ID = @secId AND
GRADE_TYPE_CODE = @grdTyCd AND
GRADE_CODE_OCCURRENCE = @grdCdOcc) AND
MAX_GRADE >= NUMERIC_GRADE AND MIN_GRADE <= NUMERIC_GRADE
Open @CrsrVar
Fetch Next From @CrsrVar
Into @LetterVal, @Numeric
While (@@FETCH_STATUS = 0)
BEGIN
Fetch Next From @CrsrVar
Into @LetterVal, @Numeric
END
Close @CrsrVar
Deallocate @CrsrVar
And below is the "test query" I'm using:
-- *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
@Numeric int, -- Numeric Grade
@Result int -- Procedure Status (0 = OK)
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,
@Numeric output, @LetterVal output
If @Result = 0
SELECT @LetterVal grade, @Numeric number
Else
SELECT 'No Find'
Execute @Result = dbo.ap_CalcGrade 100, 155, 'PA', 5,
@Numeric output, @LetterVal output
If @Result = 0
SELECT @LetterVal grade, @Numeric number
Else
SELECT 'No Find'
Execute @Result = dbo.ap_CalcGrade 105, 155, 'PA', 5,
@Numeric output, @LetterVal output
If @Result = 0
SELECT @LetterVal grade, @Numeric number
Else
SELECT 'No Find'
This is resulting in an output of:
A+ 97
A+ 97
C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
A+ 97
No Find
C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so I would really appreciate any ones advice as to whats causing the errant results, and if there is any better way I should be writing it. Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1.Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2.Outputs the numeric grade and the letter grade back to the user
3.If the numeric grade is found, return 0, otherwise return 1
4.You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
Thanks!
September 18, 2015 at 12:56 pm
hyper50 (9/18/2015)
4.You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
WHAAAAAAT????? :w00t:
That's actually the best way to return the values by using a cursor. And the best way to prove that a cursor is the worst option available.
The problem is that you're not defining your return status. You should use the RETURN statement for this.
The stored procedure should return a recordset, but I won't go much further. The correct way of getting points 1 to 3 would be like this:
CREATE PROCEDURE [dbo].[ap_CalcGrade2]
-- 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
SELECT LetterVal = LETTER_GRADE,
Numeric = NUMERIC_GRADE
FROM [dbo].[GRADE]
JOIN GRADE_CONVERSION ON MAX_GRADE >= NUMERIC_GRADE
AND MIN_GRADE <= NUMERIC_GRADE
WHERE STUDENT_ID = @studId
AND SECTION_ID = @secId
AND GRADE_TYPE_CODE = @grdTyCd
AND GRADE_CODE_OCCURRENCE = @grdCdOcc;
IF @@ROWCOUNT = 0
RETURN 1;
ELSE
RETURN 0;
GO
September 18, 2015 at 1:12 pm
It would be helpful if you were to provide the DDL for the tables involved, along with some test data.
There are 2 reasons you are getting the first pair of values returned twice instead of 'No Find' the second time:
1. You are never reintializing the variables in your test code so they will have the same value since they are in the same batch in your test.
2. The stored procedure is always returning 0 because you aren't changing the return value when nothing is found and the stored procedure is not having an errors so it will always return 0.
I want to congratulate you on actually writing set-based code to find out the letter grade from GRADE_CONVERSION. Unless you have overlapping values in MAX_GRADE or MIN_GRADE, your current cursor query should always return only 1 row. You don't even need the cursor with the code you wrote in the SP, so you might want to rethink the how you are using the cursor as well.
Just an FYI, you really don't want to solve a problem like this with a cursor, but you have been given pretty specific instructions that you need to follow to pass, but I wouldn't hire you if I presented a problem like this to you in an interview and you solved it with a cursor.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 18, 2015 at 1:55 pm
Please read some of the article on this site about cursors, The discussions are fantastic and if you are thinking of working in the DB world, and I highly recommend it (18 years and counting and still loving every minute of it).
September 18, 2015 at 2:03 pm
Luis Cazares (9/18/2015)
The correct way of getting points 1 to 3 would be like this:
CREATE PROCEDURE [dbo].[ap_CalcGrade2]
-- 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
SELECT LetterVal = LETTER_GRADE,
Numeric = NUMERIC_GRADE
FROM [dbo].[GRADE]
JOIN GRADE_CONVERSION ON MAX_GRADE >= NUMERIC_GRADE
AND MIN_GRADE <= NUMERIC_GRADE
WHERE STUDENT_ID = @studId
AND SECTION_ID = @secId
AND GRADE_TYPE_CODE = @grdTyCd
AND GRADE_CODE_OCCURRENCE = @grdCdOcc;
IF @@ROWCOUNT = 0
RETURN 1;
ELSE
RETURN 0;
GO
Luis,
You know I agree with you in principle, but for the requirements the student has been given this really isn't the correct way. They have to use a cursor. Yes the instructor is teaching them something that I don't agree with, but that is the requirement. Maybe the instructor's next assignment will be to do it without a cursor and they will discuss why the cursor is the inferior solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 18, 2015 at 2:18 pm
I completely understand, that's why I mention that it only complies with part of the requirements. However, I wanted to show a better option that will actually help in the real world. The instructor might teach the set based option later but he might not, and I didn't want to take the chance to let this go.
September 19, 2015 at 2:45 am
When I show prospective employees fresh from college, at interview, how we do things they are usually fascinated and surprised and say things like "why didn't we learn this at college"
I, too, hope that the O/Ps next lesson will be "why cursors are usually the wrong solution" but IME based on the students that come to interview my expectation, sadly, is that won't be the case.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply