trying to accept a grade(note) that is over 60% and then get the average of all the grade for a certain person

Question

This code `use Personne

insert into [dbo].[Note] ( noPersonne,cours,note)
values (25,'Math',65)

insert into personnes(nom,prenom)
values('Mark','anthony')

alter table personnes
alter column prenom varchar(50)

select* from personnes
select*from Note


use personne
alter PROCEDURE ajoutNote ( @noPersonne int,
@cours varchar(30),
@note int
)
AS

BEGIN TRAN
    IF @note>=60
    BEGIN 
        INSERT INTO note (noPersonne, cours, note)
        VALUES      (@noPersonne, @cours, @note) 
        BEGIN
            PRINT ('la note a ete rentrer avec succes')
                BEGIN
                    DECLARE @moyenne INT;

                    SET @moyenne = (SELECT AVG(note.note) AS moyenne 
                                    FROM note
                                    WHERE nopersonne=@nopersonne)

                    BEGIN
                        PRINT @moyenne
                        --set @idPersonne=(select nopersonne from Note)
                        --set @Courso=(select cours from Note)

                        --select avg(Note.note) from Note 
                        --where noPersonne=@idpersonne and  cours=@Courso
                    END
                END
        END
        SAVE TRAN f4
        RETURN;
    END
    ELSE
    BEGIN 
        ROLLBACK TRAN
    END



GO

exec ajoutNote 25,'Math',75


select *from personnes
select*from Note'

gives me this : (1 row affected) la note a ete rentrer avec succes 63 Msg 266, Level 16, State 2, Procedure ajoutNote, Line 0 [Batch Start Line 55] Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 6, current count = 7.

Completion time: 2020-02-20T14:40:26.6563562-05:00

i just want to get rid of the mistake the code does what i want.

Answer 1

You have too many BEGIN...END statements, and they seem to be overlapping as well. You also start a transaction but never commit it (SAVE TRAN only creates a savepoint, it doesn't actually commit the transaction). You don't have any error checking logic, so I don't see a need for an explicit ROLLBACK TRAN command. If the transaction fails, it will be rolled back.

So cleaning up the code and removing unneeded BEGIN...END blocks, I come up with this:

USE personne
GO

ALTER PROCEDURE ajoutNote 
( 
    @noPersonne int
    ,@cours varchar(30)
    ,@note int
)
AS
IF @note>=60
BEGIN
    BEGIN TRANSACTION f4

        INSERT INTO note 
        (
            noPersonne
            ,cours
            ,note
        )
        VALUES
        (
            @noPersonne
            ,@cours
            ,@note
        ) 

        PRINT ('la note a ete rentrer avec succes')

        DECLARE @moyenne INT;

        SELECT @moyenne = AVG(note.note) 
        FROM note
        WHERE nopersonne = @nopersonne

        PRINT @moyenne

    COMMIT TRANSACTION f4

END

Notice I move the check of IF @note>=60 outside the transaction. If @note is ever less than 60, we don't even need to attempt the transaction.

EDIT: After looking over your code again, you may be able to add COMMIT TRAN at the end of the proc to remove the error without modifying the rest of the logic of the proc, specifically the SAVE TRAN f4. I don't know your requirements, so I don't know if this technique is appropriate for your proc. But if you just add the COMMIT, your proc will look like this:

ALTER PROCEDURE ajoutNote 
( 
    @noPersonne int
    ,@cours varchar(30)
    ,@note int
)
AS
BEGIN TRAN
    IF @note>=60
    BEGIN 
        INSERT INTO note (noPersonne, cours, note)
        VALUES      (@noPersonne, @cours, @note) 
        BEGIN
            PRINT ('la note a ete rentrer avec succes')
                BEGIN
                    DECLARE @moyenne INT;

                    SET @moyenne = (SELECT AVG(note.note) AS moyenne 
                                    FROM note
                                    WHERE nopersonne=@nopersonne)

                    BEGIN
                        PRINT @moyenne
                        --set @idPersonne=(select nopersonne from Note)
                        --set @Courso=(select cours from Note)

                        --select avg(Note.note) from Note 
                        --where noPersonne=@idpersonne and  cours=@Courso
                    END
                END
        END
        SAVE TRAN f4
        RETURN;
    END
    ELSE
    BEGIN 
        ROLLBACK TRAN
    END

COMMIT TRAN