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