Description:
DELIMITER $$
DROP PROCEDURE IF EXISTS `sweepertest`.`usp_AdjSummarybyMember` $$
CREATE PROCEDURE `usp_AdjSummarybyMember`(
in ip_UsrId INT,
in ip_stdate date,
in ip_enddate date,
out OutmsgTotMembers int,
out OutmsgTotEmployees int
)
Begin
SET OutmsgTotMembers = (SELECT COUNT(memid) FROM sw_enrollinfo WHERE usrid = ip_usrid);
SET OutmsgTotEmployees = (SELECT COUNT(en.memid) FROM sw_enrollinfo en left outer join sw_meminfo mm
on en.memid=mm.memid WHERE mm.MemClsId in(1,6) and en.usrid = ip_usrid);
drop temporary table if exists sw_enrollinfo2;
create temporary table sw_enrollinfo2 as select * from sw_enrollinfo;
update sw_enrollinfo2 set memclsid=1 where memclsid=6;
SELECT
mm.descr as MemberType
,coalesce(count(distinct en.memid),0) as 'TotalMembers'
,coalesce(convert(sum(swc.bldchrg),decimal(16,2)),0) as 'TotalBilledCharges'
,coalesce(convert(sum(swc.cob),decimal(16,2)),0) as 'COBOthersPay'
,coalesce(convert(sum(swc.bldchrg)-sum(ElgChrg),decimal(16,2)),0) as 'IneligibleCharges'
,coalesce(convert(sum(swc.ElgChrg),decimal(16,2)),0) as 'EligbileCharges'
,coalesce(convert(sum(swc.NwDisc),decimal(16,2)),0) as 'NetworkDiscount'
,coalesce(convert(sum(swc.ElgChrg)-sum(swc.NwDisc),decimal(16,2)),0) as 'DiscountedEligbileCharges'
,coalesce(count(distinct swc.memid),0) as 'TotalClaimants'
,coalesce(count(swc.clmid),0) as 'TotalClaims'
,coalesce(convert(sum(swc.claimpaid),decimal(16,2)),0) as 'ClaimsPaid'
,coalesce(convert(sum(swc.Deduc),decimal(16,2)),0) as 'Deductible'
,coalesce(convert(sum(swc.Copay),decimal(16,2)),0) as 'Copay'
,coalesce(convert(sum(swc.Emppay),decimal(16,2)),0) as 'Coinsurance'
,coalesce(convert(sum(swc.claimpaid)-(sum(swc.Deduc+swc.Copay+swc.Emppay)),decimal(16,2)),0) as 'Total Employer ClaimsPaid'
FROM sw_enrollinfo2 en
left outer join sw_claims swc on swc.memid=en.memid
right outer join sw_Memclf mm on en.Memclsid=mm.Memclsid
and en.enrdt between ip_Stdate and ip_EndDate and en.usrid=ip_UsrId
group by mm.Memclsid having mm.memclsid <>6;
End $$
DELIMITER ;
How to repeat:
Hi
I am Execute the above stored procedure .
it is executed fine but
I am getting error from front end.
error like"There is already an open DataReader associated with this Connection which must be closed first."
plz give me soultion where is the problem in my SP.
Thanks
Hari
Description: DELIMITER $$ DROP PROCEDURE IF EXISTS `sweepertest`.`usp_AdjSummarybyMember` $$ CREATE PROCEDURE `usp_AdjSummarybyMember`( in ip_UsrId INT, in ip_stdate date, in ip_enddate date, out OutmsgTotMembers int, out OutmsgTotEmployees int ) Begin SET OutmsgTotMembers = (SELECT COUNT(memid) FROM sw_enrollinfo WHERE usrid = ip_usrid); SET OutmsgTotEmployees = (SELECT COUNT(en.memid) FROM sw_enrollinfo en left outer join sw_meminfo mm on en.memid=mm.memid WHERE mm.MemClsId in(1,6) and en.usrid = ip_usrid); drop temporary table if exists sw_enrollinfo2; create temporary table sw_enrollinfo2 as select * from sw_enrollinfo; update sw_enrollinfo2 set memclsid=1 where memclsid=6; SELECT mm.descr as MemberType ,coalesce(count(distinct en.memid),0) as 'TotalMembers' ,coalesce(convert(sum(swc.bldchrg),decimal(16,2)),0) as 'TotalBilledCharges' ,coalesce(convert(sum(swc.cob),decimal(16,2)),0) as 'COBOthersPay' ,coalesce(convert(sum(swc.bldchrg)-sum(ElgChrg),decimal(16,2)),0) as 'IneligibleCharges' ,coalesce(convert(sum(swc.ElgChrg),decimal(16,2)),0) as 'EligbileCharges' ,coalesce(convert(sum(swc.NwDisc),decimal(16,2)),0) as 'NetworkDiscount' ,coalesce(convert(sum(swc.ElgChrg)-sum(swc.NwDisc),decimal(16,2)),0) as 'DiscountedEligbileCharges' ,coalesce(count(distinct swc.memid),0) as 'TotalClaimants' ,coalesce(count(swc.clmid),0) as 'TotalClaims' ,coalesce(convert(sum(swc.claimpaid),decimal(16,2)),0) as 'ClaimsPaid' ,coalesce(convert(sum(swc.Deduc),decimal(16,2)),0) as 'Deductible' ,coalesce(convert(sum(swc.Copay),decimal(16,2)),0) as 'Copay' ,coalesce(convert(sum(swc.Emppay),decimal(16,2)),0) as 'Coinsurance' ,coalesce(convert(sum(swc.claimpaid)-(sum(swc.Deduc+swc.Copay+swc.Emppay)),decimal(16,2)),0) as 'Total Employer ClaimsPaid' FROM sw_enrollinfo2 en left outer join sw_claims swc on swc.memid=en.memid right outer join sw_Memclf mm on en.Memclsid=mm.Memclsid and en.enrdt between ip_Stdate and ip_EndDate and en.usrid=ip_UsrId group by mm.Memclsid having mm.memclsid <>6; End $$ DELIMITER ; How to repeat: Hi I am Execute the above stored procedure . it is executed fine but I am getting error from front end. error like"There is already an open DataReader associated with this Connection which must be closed first." plz give me soultion where is the problem in my SP. Thanks Hari