mardi 24 juillet 2018

MYSQL execute stored procedure in if statement or run complicated SET statement

I've got a table where the credits and debits switch when being imported occasionally - so I've got some code to set a variable and then swap if it matches, however I can't get it to run in an IF statement, I've tried to put it in a stored procedure but can't run the stored procedure in an IF statement either. What would you recommend? see below for code. thanks

set @TotalCr = (select max(TotalCredit) from mbs_headers);

If (@TotalCr = (select sum(PDebit) from mbs_details) and @TotalCr =! (select sum(PCredit) from mbs_details)) 

then UPDATE mbs_detailstest SET PCredit=@tmp:=PCredit, PCredit=PDebit, PDebit=@tmp 
else 
end;

OR

set @TotalCr = (select max(TotalCredit) from mbs_headers);

If (@TotalCr = (select sum(PDebit) from mbs_details) and @TotalCr =! (select sum(PCredit) from mbs_details)) 

then exec TestSwapProc 
else 
end;

with TestSwapProc containing only

UPDATE mbs_detailstest SET PCredit=@tmp:=PCredit, PCredit=PDebit, PDebit=@tmp

Aucun commentaire:

Enregistrer un commentaire