sql 统计表中所有不为空字段的条目总和

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[countColNum]
@tname varchar(100),
@sums INT output
as
begin
DECLARE @cols INT
DECLARE @_t INT
DECLARE @i INT
DECLARE @colName nvarchar(50)
DECLARE @sql nvarchar(200)
select @cols=count(name) from syscolumns where id=(select max(id)
from sysobjects where xtype='u' and name=@tname)
set @i=1
set @sums=0
while @i<@cols+1
begin
set @colName=col_name(object_id(@tname),@i)

--''在SQL中是能够隐式转换为int型的，转化后的值为0,比较字段值是否为空不能排

--除值为0的情况，必须加入len(colname)>0加入判断条件
set @sql='select @_t=count(*) from '+@tname+' where ('+@colName+' is not null and len('+@colName+')>0)'
print @sql
set @i=@i+1
exec sp_executesql @sql,N'@_t int output',@_t output
set @sums=@sums+@_t
end
print @sums
return @sums
end