create PROCEDURE PR_Credit_Total(
    in newidpiece int,
    in newnpiece bigint,
    in newnop varchar(60),
    in newdateengagement date,
    in newdatefacture date,
    in newlocalité varchar(50),
    in newtournee int,
    in newnpolice bigint,
    in newservice varchar(20),
    in newmontant float,
    in newecheance varchar(7),
    in newcreated varchar(25),
    in eventvarchar(100)
)
BEGIN
    set @total=(SELECT sum(Total_Crédit) from credit_electricité) ;
    if event=='ajouter' 
    and newtournee is null
        THEN
        if @total-newmontant>=0

                INSERT into vignetteoneep VALUES(newidpiece,newnpiece,newnop,newdateengagement,
                newdatefacture,newlocalité,newtournee,newnpolice,newservice,newmontant,newecheance,newcreated);
                then 
                UPDATE credit_electricité set Total_Crédit=Total_Crédit-newmontant;
        end if;
    end if;

        

END

CodePudding user response：

create PROCEDURE PR_Credit_Total(
    @newidpiece AS int,
    @newnpiece AS bigint,
    @newnop AS varchar(60),
    @newdateengagement AS date,
    @newdatefacture AS date,
    @newlocalité AS varchar(50),
    @newtournee AS int,
    @newnpolice AS bigint,
    @newservice AS varchar(20),
    @newmontant AS float,
    @newecheance AS varchar(7),
    @newcreated AS varchar(25),
    @event AS varchar(100),
    @total AS INT
)
AS
BEGIN
    set @total=(SELECT sum(Total_Crédit) from credit_electricité)
    if @event = 'ajouter' and @newtournee is null

        if @total - @newmontant >=0

                INSERT into vignetteoneep VALUES(newidpiece,newnpiece,newnop,newdateengagement,
                newdatefacture,newlocalité,newtournee,newnpolice,newservice,newmontant,newecheance,newcreated)

                UPDATE credit_electricité set Total_Crédit=Total_Crédit - @newmontant
END

CodePudding user response：

Here's a list of syntax errors to fix

in eventvarchar(100) missing space ,should be in event varchar(100) but event is a keyword and best avoided

if event=='ajouter' null safe equal in mysql is <=> so should be if event <=> 'ajouter' but = would do

The second if does not have a then it's not clear if the insert and update should be part of same condition but I suspect not

   if @total-newmontant>=0 then

            INSERT ...
     else  
            UPDATE ...
    end if;

And you may need to set delimiters