新手提问——求一存储过程
有这样一个表,存储工资数据
--------------------------------------------
name year month fin
a 2007 1 3000.00
b 2007 1 3200.00
c 2007 1 3150.00
--------------------------------------------
我希望用存储过程获取表中每一行的数据,然后在月份上进行递增,把改变后的数据插入到数据库,表将变成下面的样子
--------------------------------------------
name year month fin
a 2007 1 3000.00
b 2007 1 3200.00
c 2007 1 3150.00
a 2007 2 3000.00
b 2007 2 3200.00
c 2007 2 3150.00
--------------------------------------------
该存储过程如何实现?请指教。
------解决方案--------------------create table test([name] varchar(10),[year] int,[month] int,fin money)
insert into test select 'a ',2007,1,3000.00
insert into test select 'b ',2007,1,3200.00
insert into test select 'c ',2007,1,3150.00
go
create procedure sp_test
as
insert into test([name],[year],[month],fin)
select
b.name,
(case a.[month] when 12 then a.[year]+1 else a.[year] end),
(case a.[month] when 12 then 1 else a.[month]+1 end),
b.fin
from
(select top 1 [year],[month] from test order by [year] desc,[month] desc) a,
test b
where
a.[year]=b.[year] and a.[month]=b.[month]
go
exec sp_test
select * from test
/*
name year month fin
---------- ----------- ----------- ---------------------
a 2007 1 3000.0000
b 2007 1 3200.0000
c 2007 1 3150.0000
a 2007 2 3000.0000
b 2007 2 3200.0000
c 2007 2 3150.0000
*/
go
exec sp_test
select * from test
/*
name year month fin
---------- ----------- ----------- ---------------------
a 2007 1 3000.0000
b 2007 1 3200.0000
c 2007 1 3150.0000
a 2007 2 3000.0000
b 2007 2 3200.0000
c 2007 2 3150.0000
a 2007 3 3000.0000
b 2007 3 3200.0000
c 2007