这样?
CREATE PROCEDURE [OWNER].[PROCEDURE NAME]
@begindate datetime,
@enddate datetime,
@tbname varchar(20)
asexec('select sum(hours) as 工时,
sum(manfee) as 工费,
sum(materialfee) as 料费,
sum(extratax) as 附加费,
sum(extratax+manfee+materialfee) as 总收入
from '+@tbname+
'where checkdate>'+@begindate+' and finishdate<'+@enddate)
CREATE PROCEDURE [OWNER].[PROCEDURE NAME]
@begindate datetime,
@enddate datetime,
@tbname varchar(20)
asexec('select sum(hours) as 工时,
sum(manfee) as 工费,
sum(materialfee) as 料费,
sum(extratax) as 附加费,
sum(extratax+manfee+materialfee) as 总收入
from '+@tbname+
'where checkdate>'+@begindate+' and finishdate<'+@enddate)
用一个视图!
create view jsdchang as
select jsdchang1,* from jsdchang1 union all
select jsdchang2,* from jsdchang2 union all
select jsdchang3,* from jsdchang3 union all
select jsdchang4,* from jsdchang4 union all
select jsdchang5,* from jsdchang5 union all
select jsdchang6,* from jsdchang6 然后把你的查询语句这样改
select sum(hours) as 工时,sum(manfee) as 工费,sum(materialfee) as 料费,
sum(extratax) as 附加费,sum(extratax+manfee+materialfee) as 总收入
from jsdchang c
where c.checkdate>'2005-01-01' and c.finishdate<'2006-04-06'
group by jsdchang--如果不建视图也可以
select sum(hours) as 工时,sum(manfee) as 工费,sum(materialfee) as 料费,
sum(extratax) as 附加费,sum(extratax+manfee+materialfee) as 总收入
from jsdchang6 c6
where c6.checkdate>'2005-01-01' and c6.finishdate<'2006-04-06'
union all
select sum(hours) as 工时,sum(manfee) as 工费,sum(materialfee) as 料费,
sum(extratax) as 附加费,sum(extratax+manfee+materialfee) as 总收入
from jsdchang5 c6
where c6.checkdate>'2005-01-01' and c6.finishdate<'2006-04-06'
union all ...
以下类似意思就是用union all把六个结果连成一个结果集
(
@begindate datetime,
@enddate datetime,
@tbname varchar(20)
)
as
declare @sql varchar(8000),@cn int
select @sql='',@cn=1
while @cn<=6
begin
select @sql= @sql+case when @sql<>'' then ' union ' else '' end
+ 'select sum(hours) as 工时,
sum(manfee) as 工费,
sum(materialfee) as 料费,
sum(extratax) as 附加费,
sum(extratax+manfee+materialfee) as 总收入 '+
'from '+@tbname+cast(@cn as nvarchar(10))+
' where checkdate>'''+cast(@begindate as nvarchar(10))+''' and finishdate<'''+cast(@enddate as nvarchar(10))+''''
set @cn=@cn+1
--print @sql
exec(@sql)
end
go
--drop PROCEDURE dbo.mysp
(
@begindate datetime,
@enddate datetime,
@tbname varchar(20)
)
as
declare @sql varchar(8000),@cn int
select @sql='',@cn=1
while @cn<=6
begin
select @sql= @sql+case when @sql<>'' then ' union ' else '' end
+ 'select sum(hours) as 工时,
sum(manfee) as 工费,
sum(materialfee) as 料费,
sum(extratax) as 附加费,
sum(extratax+manfee+materialfee) as 总收入 '+
'from '+@tbname+cast(@cn as nvarchar(10))+
' where checkdate>'''+cast(@begindate as nvarchar(10))+''' and finishdate<'''+cast(@enddate as nvarchar(10))+''''
set @cn=@cn+1
--print @sql
end
exec(@sql)
go
--drop PROCEDURE dbo.mysp
从 日期:_____________ 到 日期_______________
部门 工号 工费 料费 附加费 总收入
一厂
二厂
三厂
四厂
五厂
六厂
合计
以上这个是显示给用户的页面,上面的条件是变的也就是说select sum(hours) as 工时,sum(manfee) as 工费,sum(materialfee) as 料费,
sum(extratax) as 附加费,sum(extratax+manfee+materialfee) as 总收入
from jsdchang c
where c.checkdate>'2005-01-01' and c.finishdate<'2006-04-06'
group by jsdchang--如果不建视图也可以
select sum(hours) as 工时,sum(manfee) as 工费,sum(materialfee) as 料费,
sum(extratax) as 附加费,sum(extratax+manfee+materialfee) as 总收入
from jsdchang6 c6 union all
select sum(hours) as 工时,sum(manfee) as 工费,sum(materialfee) as 料费,
sum(extratax) as 附加费,sum(extratax+manfee+materialfee) as 总收入
from jsdchang5 c6
union all ...
上面都不带where条件