存储过程脚本:
CREATE DEFINER=`root`@`%` PROCEDURE `CalcCompletionPercentByItem`(IN `pitemId` varchar(50),out `c` decimal(15,2))
label:BEGIN
declare taskcnt int;
declare FinishedCnt int;
set taskcnt=(select count(1) from itemtask where ItemId=pitemId and isdelete=0);
-- select taskcnt;
-- leave label;
set FinishedCnt =(select count(1) from itemtask where ItemId=pitemId and isdelete=0 and State=2);
if taskcnt=0 then
set c=0;
leave label;
end if;
set c=convert(FinishedCnt/taskCnt*100,decimal(15,2));
SELECT c;
END
.netCore调用:
var pitemId = new SugarParameter("@pitemId", itemId);
var ReturnV = new SugarParameter("@c", null, true);
ReturnV.DbType = System.Data.DbType.Decimal;
await Db.Ado.UseStoredProcedure().SqlQuerySingleAsync<float>("CalcCompletionPercentByItem", pitemId, ReturnV);
return (decimal)ReturnV.Value;