none
Help creating stored procedure with cursor loop RRS feed

  • Question

  • 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!


    Friday, September 18, 2015 6:24 PM

Answers

  • Ok, try the following:

    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
    
    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
    
    --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'
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, September 18, 2015 7:46 PM
    Moderator

All replies

  • Post structures of your tables, some input data and desired result. You seem to be selecting from 2 tables, but you're not using aliases or JOIN condition, so it's really not clear at all what you're doing. In your CURSOR loop you also don't do anything.

    Also, why the class assignment in asking you to use a cursor? In SQL Server there are very few cases when you need to use cursor as SQL Server works best as set based operations. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, September 18, 2015 6:33 PM
    Moderator
  • Try setting @result back to 0 at the beginning of the loop.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Friday, September 18, 2015 7:18 PM
  • Sorry, not sure what the best way of sharing this would be, but the structure of the GRADE table is:

    CREATE TABLE [dbo].[GRADE](
    [STUDENT_ID] [int] NOT NULL,
    [SECTION_ID] [int] NOT NULL,
    [GRADE_TYPE_CODE] [char](2) NOT NULL,
    [GRADE_CODE_OCCURRENCE] [bigint] NOT NULL,
    [NUMERIC_GRADE] [tinyint] NOT NULL,
    [COMMENTS] [varchar](2000) NULL,
    [CREATED_BY] [varchar](30) NOT NULL,
    [CREATED_DATE] [date] NOT NULL,
    [MODIFIED_BY] [varchar](30) NOT NULL,
    [MODIFIED_DATE] [date] NOT NULL

    And the structure of the GRADE_CONVERSION table is: 

    CREATE TABLE [dbo].[GRADE_CONVERSION](
    [LETTER_GRADE] [varchar](2) NOT NULL,
    [GRADE_POINT] [decimal](3, 2) NULL,
    [MAX_GRADE] [tinyint] NOT NULL,
    [MIN_GRADE] [tinyint] NOT NULL,
    [CREATED_BY] [varchar](30) NOT NULL,
    [CREATED_DATE] [date] NOT NULL,
    [MODIFIED_BY] [varchar](30) NOT NULL,
    [MODIFIED_DATE] [date] NOT NULL,

    My query is pulling "LETTER_GRADE" from the GRADE_CONVERSION table, and the "NUMERIC_GRADE" is coming from the GRADE table.  It's using the input to find the NUMERIC_GRADE on GRADE table, and uses that as the criteria on GRADE_CONVERSION table to get the corresponding LETTER_GRADE.  As for the cursor loop "not doing anything", this is where I'm lost...  and unfortunately the assignment requires that I "must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade".  Any help or advice would be much appreciated...  Thanks.

    Friday, September 18, 2015 7:19 PM
  • That's great, but can you give us some example data (preferably in the form of insert statements) and some expected output for the example data?


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Friday, September 18, 2015 7:25 PM
  • Ok, try the following:

    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
    
    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
    
    --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'
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, September 18, 2015 7:46 PM
    Moderator
  • Also, you need to ADD GO statement between each of your tests and re-declare your variables. Otherwise it's not changing the values in each execution.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, September 18, 2015 7:50 PM
    Moderator