create procedure pro_1
--drop procedure pro_1
asbegin
if object_id('##t_sunshare') is not null
drop table ##t_sunshare
create table ##t_sunshare(a int)
declare @name varchar(50)
declare @sql varchar(8000)
declare @sum int
select @sum=0
declare my_cursor cursor for
select name from syscolumns where id =object_id('t_1') and typestat<>'1'
open my_cursor
fetch my_cursor into @name
while @@fetch_status=0
begin
set @sql=' insert into ##t_sunshare select sum(isnull(convert(int,'+@name+'),0)) from WCMEXCELH'
exec(@sql)
fetch my_cursor into @name
end
close my_cursor
deallocate my_cursor
select sum(isnull(a,0)) as value from ##t_sunshare
drop table ##t_sunshare
endGO
--drop procedure pro_1
asbegin
if object_id('##t_sunshare') is not null
drop table ##t_sunshare
create table ##t_sunshare(a int)
declare @name varchar(50)
declare @sql varchar(8000)
declare @sum int
select @sum=0
declare my_cursor cursor for
select name from syscolumns where id =object_id('t_1') and typestat<>'1'
open my_cursor
fetch my_cursor into @name
while @@fetch_status=0
begin
set @sql=' insert into ##t_sunshare select sum(isnull(convert(int,'+@name+'),0)) from WCMEXCELH'
exec(@sql)
fetch my_cursor into @name
end
close my_cursor
deallocate my_cursor
select sum(isnull(a,0)) as value from ##t_sunshare
drop table ##t_sunshare
endGO
--不考虑NULL值和变量超长declare @s varchar(8000)
set @s=''
select @s=@s+',['+name+']' from syscolumns where id=object_id('你的表名')
set @s=substring(@s,2,8000)
exec('select sum('+@s+') from 表')
set @s=''
select @s=@s+'+['+name+']' from syscolumns where id=object_id('表')
set @s=substring(@s,2,8000)
exec('select sum('+@s+') from 表')
create table 表(a1 int,a2 int,a3 int,a4 int)
insert 表 select 1,2,3,4
union all select 2,2,2,2
godeclare @s varchar(8000)
set @s=''
select @s=@s+'+['+name+']' from syscolumns where id=object_id('表')
set @s=substring(@s,2,8000)
exec('select sum('+@s+') from 表')
go--删除测试
drop table 表/*--测试结果
-----------
18--*/