[存储过程]sql server 中 统计一条记录中 非空 字段个数 ,并且计算它所占百分比

integrity

是表中存放非空字段百分比 的字段

companyName

等字段是参与统计的成员

看代码

 
drop procedure update_company_intergrity
;
create procedure update_company_intergrity @id int
as

update manager_company   set integrity =
(
SELECT 
	(
	len(rtrim(ltrim(SubString(isnull(companyName,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(productFirstType_ID,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(linkManName,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(linkManPost,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyTel,''),1,1))))
 
	+len(rtrim(ltrim(SubString(isnull(companyAdr,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(companyMobile,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyFax,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(companyHomepage,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyDelegate,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(companyDelegateCall,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(companyMobile,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyBusinessModel,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(companyArea,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(companyIntroduction,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(businessRegistrationNumber,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(registeredCapital,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(enterpriseType,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(registeredDate,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(registeredAuthority,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(inspectionDate,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(companyScale,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(style_ID,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(mainProducts,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(province,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(city,''),1,1))))
  +len(rtrim(ltrim(SubString(isnull(manageRange,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(turnoverOfYear,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(postalcode,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(stapleMarket,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(openBank,''),1,1))))
  +len(rtrim(ltrim(SubString(STR(isnull(isOfferProcessAndService,'')),1,1))))
	+len(rtrim(ltrim(SubString(isnull(importOfYear,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(exitOfYear,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(researchNum,''),1,1))))

	+len(rtrim(ltrim(SubString(isnull(outputOfMonth,''),1,1))))
 
  
  +len(rtrim(ltrim(SubString(isnull(mainIndustry,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(registerAdr,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(mainCustom,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(mainManagePlace,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(mangerBrand,''),1,1))))

  +len(rtrim(ltrim(SubString(isnull(bankNum,''),1,1))))
	+len(rtrim(ltrim(SubString(isnull(qualityControl,''),1,1))))
 	+len(rtrim(ltrim(SubString(isnull(areaNum,''),1,1))))
	+len(rtrim(ltrim(SubString(STR(isnull(certificateNum,'')),1,1))))
	+len(rtrim(ltrim(SubString(isnull(effectTime,''),1,1))))
	 )
	 *100/45
FROM manager_companydetails 
where  rtrim(ltrim(manager_companydetails.id)) = rtrim(ltrim(@id))
)
where rtrim(ltrim(manager_company.id)) = rtrim(ltrim(STR(@id)))
 
;