CREATE Procedure SelectMonthSecno
@PrtWokNo Varchar(50),
@PrtName   Varchar(50),
@PrtSecNo Varchar(50),
@StaTime   DateTime,
@EndTime   DateTime
as
Declare @EveMonth DateTime,@YearMonth Varchar(500),@Str Varchar(8000)
Declare @int int,@IntYear int,@IntMonth int,@StaYear int,@StaMonth int,@EndYear int,@EndMonth int
Declare @Year int,@Month int,@Day int,@GetDate DateTime
Declare @SelPrtStr Varchar(8000)    –用来存储查询的语句
Set @Str = \'(\’
Set @SelPrtStr = \’Select Distinct Month,SecNo\’   –27
if @PrtWokNo is not null
    Set @SelPrtStr = @SelPrtStr + \’,WokNo\’        –33
if @PrtName is not null
    Set @SelPrtStr = @SelPrtStr + \’,Name\’         –32,38
Set @SelPrtStr = @SelPrtStr + \’ from AttPrt \’    –39,44,45,50
Select @year=Datepart(Year,GetDate()),@month=Datepart(Month,GetDate()),@day=Datepart(Day,GetDate())  
Set @GetDate = (str(@year,len(@year)) + \’-\’ + str(@month,len(@month)) + \’-\’ + str(@day,len(@day)))
if @EndTime >= @GetDate
    Set @EndTime = GetDate() – 1
if @PrtWokNo is not null
    Set @SelPrtStr = @SelPrtStr + \’ Where WokNo = \’   + \’\’\’\’ + @PrtWokNo + \’\’\’\’   
if @PrtName is not null
    Begin
       if len(@SelPrtStr) in (39,44,45,50)
          Set @SelPrtStr = @SelPrtStr + \’ Where Name = \’ + \’\’\’\’ + @PrtName + \’\’\’\’
       else
          Set @SelPrtStr = @SelPrtStr + \’ and Name = \’ + \’\’\’\’ + @PrtName + \’\’\’\’
    End  
if @PrtSecNo is not null
    Begin
       if len(@SelPrtStr) in (39,44,45,50)
          Set @SelPrtStr = @SelPrtStr + \’ Where SecNo = \’ + \’\’\’\’ + @PrtSecNo + \’\’\’\’
       else
          Set @SelPrtStr = @SelPrtStr + \’ and SecNo = \’ + \’\’\’\’ + @PrtSecNo + \’\’\’\’
    End
Select @int = 0,@StaYear = Year(@StaTime),@StaMonth = Month(@StaTime),@EndYear = Year(@EndTime),@EndMonth = Month(@EndTime)
Select @IntYear = @EndYear – @StaYear
if @IntYear = 0
    Set @IntMonth = @EndMonth – @StaMonth
Else
    Set @IntMonth = 12 * @IntYear + @EndMonth – @StaMonth
    While @int <= @IntMonth
          Begin
             Select @EveMonth = DateAdd(Month,@int,@StaTime)    
             if len(Month(@EveMonth)) = 2
                   Set @YearMonth = Str(Year(@EveMonth),len(Year(@EveMonth))) + \’.\’ + Str(Month(@EveMonth),len(Month(@EveMonth)))
             else
                   Set @YearMonth = Str(Year(@EveMonth),len(Year(@EveMonth))) + \’.0\’ + Str(Month(@EveMonth),len(Month(@EveMonth)))  
             Set @Str = @Str + @YearMonth   + \’,\’
             Set @int = @int + 1  
          End  
Select @Str = SubString(@Str,0,len(@Str)) + \’)\’
if len(@SelPrtStr) in (39,44,45,50)
    Set @SelPrtStr = @SelPrtStr + \’ Where Month in \’ + @Str + \’ Order by Month,SecNo\’    
else
    Set @SelPrtStr = @SelPrtStr + \’ and Month in \’ + @Str + \’ Order by Month,SecNo\’    
Execute (@SelPrtStr)
GO