查找考勤记录要打印的存储过程_感悟大智慧

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

转载于:https://www.cnblogs.com/Ext0755/archive/2010/11/20/1882588.html