一个存储过程

SE [SXW]
GO
/****** 对象:  StoredProcedure [dbo].[UserVisitorCountByMonthForDay]    脚本日期: 09/19/2010 11:36:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UserVisitorCountByMonthForDay]
 -- Add the parameters for the stored procedure here
 (@month int)
AS

BEGIN Transaction T
      
      Create Table #tempdt
 (
  DayD int,--天
  Num1 int, --总数量
  Num2 int, --游客数量
        Num3 int, --非游客数量
     )
 --总数量
 --定义一个游标
    DECLARE DayDAll CURSOR LOCAL SCROLL FOR 
 select datepart(day,Sdate) as DayDAll,count(sdate) as Num1 from XTGL_UserVisitDetails where datepart(month,Sdate)= @month group by datepart(day,Sdate) order by datepart(day,Sdate)
    DECLARE @DayDAll int --天
 DECLARE @Num1 int --数量
    OPEN DayDAll 
    FETCH NEXT FROM DayDAll INTO @DayDAll,@Num1
     WHILE @@FETCH_STATUS = 0
    begin
  insert into #tempdt(DayD,Num1) values(@DayDAll,@Num1)
  FETCH NEXT FROM DayDAll INTO @DayDAll,@Num1
    end
    CLOSE  DayDAll 
    DEALLOCATE DayDAll 
    
 --游客数量
 --定义一个游标
    DECLARE DayDYouKe CURSOR LOCAL SCROLL FOR 
 select datepart(day,Sdate) as DayDYouKe,count(sdate) as Num2 from XTGL_UserVisitDetails where datepart(month,Sdate)= @month and UserID='游客' group by datepart(day,Sdate) order by datepart(day,Sdate)
    DECLARE @DayDYouKe int --天
 DECLARE @Num2 int --数量
    OPEN DayDYouKe 
    FETCH NEXT FROM DayDYouKe INTO @DayDYouKe,@Num2
     WHILE @@FETCH_STATUS = 0
    begin
  update #tempdt set Num2=@Num2 where DayD=@DayDYouKe
  FETCH NEXT FROM DayDYouKe INTO @DayDYouKe,@Num2
    end
    CLOSE  DayDYouKe 
    DEALLOCATE DayDYouKe

 --非游客数量
 --定义一个游标
    DECLARE DayDNYouKe CURSOR LOCAL SCROLL FOR 
 select datepart(day,Sdate) as DayDNYouKe,count(sdate) as Num3 from XTGL_UserVisitDetails where datepart(month,Sdate)= @month and UserID<>'游客' group by datepart(day,Sdate) order by datepart(day,Sdate)
    DECLARE @DayDNYouKe int --天
 DECLARE @Num3 int --数量
    OPEN DayDNYouKe 
    FETCH NEXT FROM DayDNYouKe INTO @DayDNYouKe,@Num3
     WHILE @@FETCH_STATUS = 0
    begin
  update #tempdt set Num3=@Num3 where DayD=@DayDNYouKe
  FETCH NEXT FROM DayDNYouKe INTO @DayDNYouKe,@Num3
    end
    CLOSE  DayDNYouKe 
    DEALLOCATE DayDNYouKe

 update #tempdt set Num1=isnull(Num1,0),Num2=isnull(Num2,0),Num3=isnull(Num3,0)
 IF @@Error <> 0
 Begin
    RollBack Transaction T
 End
 Else
    Commit Transaction T

    select DayD as '日期',Num1 as '总数',Num2 as '游客浏览次数',Num3 as '注册用户浏览次数' from #tempdt order by DayD
     
--END