use soft--打开数据库
--判断存储过程是否存在
SET NOCOUNT On
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[yearCountTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[yearCountTest]
go
--创建存储过程
create procedure yearCountTest
(@currentYear int)
as
begin
---创建临时表
SET NOCOUNT ON
---创建临时表
IF EXISTS(SELECT NAME FROM tempdb.dbo.sysobjects WHERE NAME LIKE '#TempTable%' AND type='U')
DROP TABLE #TempTable ---删除临时表
CREATE TABLE #TempTable
(
employeeID int primary key,
employeeName varchar(50),
oneMonth int,
towMonth int,
threeMonth int,
fourMonth int,
fiveMonth int,
sixMonth int,
sevenMonth int,
eightMonth int,
nineMonth int,
tenMonth int,
elevenMonth int,
twelveMonth int
)
---创建临时游标
DECLARE myCursor CURSOR
for select employeeID,employee_name from employee
--定义临时变量
declare @month int --月份变量
declare @total int --统计数目
declare @empID int --员工id
declare @empName varchar(50) --员工姓名
--打开游标
open myCursor
--游标向前移动一行
fetch next from myCursor into @empID,@empName
while @@fetch_status=0
begin
if (select employeeID from #TempTable where employeeID=@empID)is null
begin
insert into #TempTable(employeeID,employeeName)values(@empID,@empName)
set @month = 1
while @month<13 --逐月循环
begin
SELECT @total=count(*) from soft_look where replace(开发者,' ','@') LIKE '%@' + CONVERT(VARCHAR(10), @empID) + '@%' and month(著作权申报日期)= @month and year(著作权申报日期)=@currentYear
if @month=1
update #TempTable set oneMonth=@total where employeeID=@empID
else if @month=2
update #TempTable set towMonth=@total where employeeID=@empID
else if @month=3
update #TempTable set threeMonth=@total where employeeID=@empID
else if @month=4
update #TempTable set fourMonth=@total where employeeID=@empID
else if @month=5
update #TempTable set fiveMonth=@total where employeeID=@empID
else if @month=6
update #TempTable set sixMonth=@total where employeeID=@empID
else if @month=7
update #TempTable set sevenMonth=@total where employeeID=@empID
else if @month=8
update #TempTable set eightMonth=@total where employeeID=@empID
else if @month=9
update #TempTable set nineMonth=@total where employeeID=@empID
else if @month=10
update #TempTable set tenMonth=@total where employeeID=@empID
else if @month=11
update #TempTable set elevenMonth=@total where employeeID=@empID
else if @month=12
update #TempTable set twelveMonth=@total where employeeID=@empID
set @month=@month+1
end
end
else
print CAST(@empID AS VARCHAR(10))+'已存在'
fetch next from myCursor into @empID,@empName
end
close myCursor --关闭游标
deallocate myCursor --删除游标
select * from #TempTable
SET NOCOUNT OFF
end
go
execute yearCountTest 2007