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

原创 2011年07月07日 13:33:43

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)))
 
;


相关文章推荐

SQL Server 学习笔记④ 查询某个数据所在数据库表中的哪个字段上出现过的存储过程

--2008查找某数据库中的列是否存在某个值 create proc spFind_Column_In_DB ( @type int,--类型:1为文字类型、2为数值类型 @str nvarcha...

sql server 单主键高效分页存储过程 (支持多字段排序)

Create PROC P_viewPage /* 适用于单一主键或存在唯一值列的表或视图 ps:Sql语句为8000...

Sql Server 不常见应用之一:获取表的基本信息、字段列表、存储过程参数列表

【推荐】Sql Server 不常见应用之一获取表的基本信息、字段列表、存储过程参数列表 ——通过知识共享树立个人品牌。    一、获取表的基本信息SELECT [TableName] = [Tabl...

sql server存储过程实例:统计一段时间内各连续ID的价格合计

有一个表如下:  ID     saleDate   Price  1      2008-1-1   40  2      2008-1-1   50  3      2008-1-2   ...

sql server存储过程学习记录

 declare @name nvarchar(20)declare @kip nvarchar(20)if Exists(Select name From sysobjects Where name...

SQL Server与MySQL存储过程学习记录之一

SQL语句执行的时候要先编译,然后执行。 存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存...
  • weiqubo
  • weiqubo
  • 2013年10月21日 14:48
  • 2361

SQL Server学习记录之存储过程

1、下面是创建存储过程SP_TFishSuperControl的脚本 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON use NewHn2015 go if ex...

Sql server存储过程以及一次插入多条记录

首先简单介绍sql server存储过程吧。至于概念含义啥的就不做过多介绍了。它其实和mysql有些类似,语法大同小异。 还是用例子说明问题吧。 CREATE PROCEDURE insert_s...

sql server存储过程的笔记记录

1·测试代码,自我记录知识点方便回忆应用 USE [AIS20111111172448]//数据库的名称 GO /****** Object:  StoredProcedure [dbo].[a...

sql server 查询所有表的字段的详细信息的存储过程(在 SQL Server 2005 中查询表结构及索引 )

SELECT TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END, TableDesc=ISNULL(CASE WH...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:[存储过程]sql server 中 统计一条记录中 非空 字段个数 ,并且计算它所占百分比
举报原因:
原因补充:

(最多只允许输入30个字)