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 previus record.

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

thie is what i need from the created table

image explanation of the problem into the table

CodePudding user response：

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.