Search code examples
sqlsql-servert-sqlstored-procedures

T-SQL stored procedure - error inserting the last variable


The last variable declared in the stored procedure @note is inserted in the previous record, could you make me understand the reason?

ALTER PROCEDURE [dbo].[orarioannuale]
    @idcorso int
AS
    DECLARE @giorno varchar(50)
    DECLARE @giornosettimana varchar(50)
    DECLARE @nomemateria varchar(500)
    DECLARE @ora int
    DECLARE @docente varchar(500)
    DECLARE @note varchar(500)
 
    CREATE TABLE #result
    (
        giorno varchar(50),
        giornosettimana varchar(50),
        nomemateria varchar(500),
        orada int,
        oraa int,
        docente varchar(500),
        note varchar (500)
    )

    DECLARE @giornoold varchar(50) = ''
    DECLARE @giornosettimanaold varchar(50) = ''
    DECLARE @docenteold varchar(50) = ''
    DECLARE @materiaold varchar(500) = ''
    DECLARE @orain int
    DECLARE @orafin int
    DECLARE @oreg int
    DECLARE @notef varchar(500)

    DECLARE vendor_cursor CURSOR FOR 
        SELECT 
            '''' + CONVERT(varchar, DATEPART(DAY, [giorno])) + '/' + CONVERT(varchar, DATEPART(MONTH, [giorno])) + '/' + CONVERT(varchar, DATEPART(YEAR, [giorno])) giornoo, 
            CASE
                WHEN [giornosettimana] = 1 THEN 'LUNEDI'''
                WHEN [giornosettimana] = 2 THEN 'MARTEDI'''
                WHEN [giornosettimana] = 3 THEN 'MERCOLEDI'''
                WHEN [giornosettimana] = 4 THEN 'GIOVEDI'''
                WHEN [giornosettimana] = 5 THEN 'VENERDI'''
                WHEN [giornosettimana] = 6 THEN 'SABATO'''
            END giornosettimana,
            NomeMateria,
            ora,
            REPLACE([Cognome], 'A-', '') docente,
            note
        FROM 
            [dbo].[calendario]
        WHERE 
            idcorso = @idcorso    --2204--2192
        ORDER BY
            giorno, ora
    
    OPEN vendor_cursor  
  
    FETCH NEXT FROM vendor_cursor   
INTO @giorno, @giornosettimana, @nomemateria, @ora, @docente, @note

    WHILE @@FETCH_STATUS = 0  
    BEGIN 
        IF @materiaold = ''
        BEGIN
            SET @giornoold = @giorno
            SET @giornosettimanaold = @giornosettimana
            SET @docenteold = @docente
            SET @materiaold = @nomemateria
            SET @orain = @ora
            SET @oreg = 0
            SET @notef = @note
        END

        IF (@giorno <> @giornoold OR @nomemateria <> @materiaold)
        BEGIN
            INSERT INTO #result 
            VALUES (@giornoold, @giornosettimanaold, @materiaold, @orain, @orain + @oreg, @docenteold, @note)

            SET @giornoold = @giorno
            SET @giornosettimanaold = @giornosettimana
            SET @docenteold = @docente
            SET @materiaold = @nomemateria
            SET @orain = @ora
            SET @oreg = 1
            SET @notef = @note
        END
        ELSE
            SET @oreg = @oreg + 1

    FETCH NEXT FROM vendor_cursor   
        INTO @giorno, @giornosettimana, @nomemateria, @ora, @docente, @note
END   

CLOSE vendor_cursor;  
DEALLOCATE vendor_cursor; 

INSERT INTO #result 
VALUES (@giornoold, @giornosettimanaold, @materiaold, @orain, @orain + @oreg, @docenteold, @notef)

SELECT
    giorno, giornosettimana, nomemateria,
    '''' + CONVERT(VARCHAR, (orada)) + '-' + CONVERT(VARCHAR, (oraa)) AS orario, 
    oraa-orada AS numeroore, 
    docente, note 
FROM
    #result

Remember, my problem to solve is: the last declared variable in this store procedure is printed/inserted into the previous record.

I'm sorry to ask this here but i really don't know the reason of this.

This is what I need from the created table:

image explanation of the problem into the table


Solution

  • Try changing the first insert from

    insert into #result values (...,@note)
    

    to:

    insert into #result values (...,@notef)
    
    

    At this point in the logic, you have detected a change in one of giorno or nomemateria and are trying to write the saved "old" data from the prior record. The saved old @notef value is what you want here, not @note.

    ADDENDUM:

    You also might be able to eliminate the cursor loop entirely by using the ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) ranking window function to assign sequence numbers within each group and then selecting the latest from each group before customizing the selected data into your final results.

    See this question for a similar problem case.

    You can start with something like:

    SELECT *
    FROM (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY giorno, giornosettimana ORDER BY ora DESC) AS RowNumInPartitonDescending,
            ROW_NUMBER() OVER(PARTITION BY giorno, giornosettimana ORDER BY ora) AS RowNumInPartitonAscending, -- oreg
            *
        FROM [dbo].[calendario]
        WHERE idcorso = @idcorso  --2204--2192
    ) A
    WHERE RowNumInPartitonDescending = 1  -- Selects the latest from each partition
    ORDER BY giorno, ora
    

    Once you have your row selection logic confirmed, you can replace the SELECT * with your customized results.