在存储过程中调用exec的问题

[herofy]

我要循环几十个表，把几十个表中的同一字段求和，
我有一个表t1存着几十个表的表名
我在存储过程中是这样写的
declare @summoney numeric(18,4)
declare @sumnumber numeric(18,4)
declare @tablename varchar(50)
declare cur_Table CURSOR
for select tablename from t1
OPEN cur_Table
FETCH Next FROM cur_Table INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
//这里是问题所在????
exec ('select sum(Money),sum(number) from '+@TableName)
//?????我怎么把求出sum(Money),sum(number)付给@summoney和@sumnumber
FETCH Next FROM cur_Table INTO @TableName
end

[txlicenhe]

http://expert.csdn.net/Expert/topic/2364/2364046.xml?temp=.829693
[交流]动态SQL语句

[xhh_88]

我丢一块砖

create table tSumMoney
(
fsummoney numeric(18,4)
fsumnumber numeric(18,4)
ftablename varchar(50)
)

declare cur_Table CURSOR
for select tablename from t1
OPEN cur_Table
FETCH Next FROM cur_Table INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('select sum(Money),sum(number),'''+@TableName+'''
into tSumMoney from '+@TableName)
FETCH Next FROM cur_Table INTO @TableName
end
/*
这里添加对结果表(tSumMoney)的处理
*/
drop table tSumMoney

[fly518]

declare @summoney numeric(18,4)
declare @sumnumber numeric(18,4)
declare @tablename varchar(50) ，
@ldc_je decimal(18,4),
@ldc_sl numeric(18,4)

declare cur_Table CURSOR
for select tablename from t1
OPEN cur_Table
FETCH Next FROM cur_Table INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
//这里是问题所在????
exec ('select @ldc_je = sum(Money), @ldc_sl = sum(number) from '+@TableName)
//?????我怎么把求出sum(Money),sum(number)付给@summoney和@sumnumber
set @summoney = @summoney + @ldc_je
set @sumnumber = @sumnumber + @ldc_je
FETCH Next FROM cur_Table INTO @TableName
end

[fly518]

sorry ， 上面的写错了，应该将exec的动态sql改为下面的写法：
exec sp_executesql 'select @ldc_je = sum(Money), @ldc_sl = sum(number) from '+@TableName , N'@ls_tbname numeric(18,4) output ，@ldc_sl numeric(18,4) output' , @ls_Tbname output , @ldc_sl output

[herofy]

up