select * from Journal where AdAgStart < 45 and AdAgEnd >=45 and AdMon=12
select * from Journal where AdAgStart < 46 and AdAgEnd >=46 and AdMon=1
select * from Journal where AdAgStart < 47 and AdAgEnd >=47 and AdMon=2
select * from Journal where AdAgStart < 48 and AdAgEnd >=48 and AdMon=3
select * from Journal where AdAgStart < 49 and AdAgEnd >=49 and AdMon=4
select * from Journal where AdAgStart < 50 and AdAgEnd >=50 and AdMon=5

45,46,47,48,49,50是递增的。知道45知道循环6次得出来的结果就是45,46,47,48,49,50

12,1,2,3,4,5 也是递增，是月份的递增

create proc pr_test
@AdAg int
as
declare @m int
set @m = month(getdate())
declare @i int
set @i = 0
while @i <= 5 
begin
   select * from Journal where AdAgStart < @AdAg + @i  and AdAgEnd >= @AdAg + @i and AdMon= @m
   set @m = @m + 1
   if @m > 12 
      set @m = 1
   set @i = @i + 1
end
go

-调用
exec pr_test 45

------解决方案--------------------
探讨


这些数据 显示到一张表中？


------解决方案--------------------
用动态SQL实现,
SQL code

declare @x int,@y int,@m int,@sql varchar(6000)

-- 用户输入的变量
select @x=45,@y=6


select @y=@x+@y-1,@m=month(getdate()),@sql=''
while(@x<=@y)
begin
  select @sql=@sql+'select * from Journal where AdAgStart<'+cast(@x as varchar(5))
              +' and AdAgEnd>='+cast(@x as varchar(5))+' and AdMon='+cast(@m as varchar(5))+';'
  select @x=@x+1,@m=case when @m+1=13 then 1 else @m+1 end
end

-- 执行@sql
exec(@sql)

-- 打印@sql
print @sql

--> 结果
select * from Journal where AdAgStart<45 and AdAgEnd>=45 and AdMon=12;
select * from Journal where AdAgStart<46 and AdAgEnd>=46 and AdMon=1;
select * from Journal where AdAgStart<47 and AdAgEnd>=47 and AdMon=2;
select * from Journal where AdAgStart<48 and AdAgEnd>=48 and AdMon=3;
select * from Journal where AdAgStart<49 and AdAgEnd>=49 and AdMon=4;
select * from Journal where AdAgStart<50 and AdAgEnd>=50 and AdMon=5;

------解决方案--------------------
try this,
SQL code

declare @x int,@y int,@m int,@sql varchar(6000)

-- 用户输入的变量
select @x=45,@y=6


select @y=@x+@y-1,@m=month(getdate()),@sql=''
while(@x<=@y)
begin
  select @sql=@sql+'select * from Journal where AdAgStart<'+cast(@x as varchar(5))
              +' and AdAgEnd>='+cast(@x as varchar(5))+' and AdMon='+cast(@m as varchar(5))
              +case when @x=@y then ' ' else ' union all ' end
  select @x=@x+1,@m=case when @m+1=13 then 1 else @m+1 end
end

-- 执行@sql
exec(@sql)

-- 打印@sql
print @sql

--> 结果
select * from Journal where AdAgStart<45 and AdAgEnd>=45 and AdMon=12 union all 
select * from Journal where AdAgStart<46 and AdAgEnd>=46 and AdMon=1 union all 
select * from Journal where AdAgStart<47 and AdAgEnd>=47 and AdMon=2 union all 
select * from Journal where AdAgStart<48 and AdAgEnd>=48 and AdMon=3 union all 
select * from Journal where AdAgStart<49 and AdAgEnd>=49 and AdMon=4 union all 
select * from Journal where AdAgStart<50 and AdAgEnd>=50 and AdMon=5

------解决方案--------------------
个人认为是动态SQL的效率高, 你可以测试一下..

如果目标表Journal数据量小的话, 其实看不出差别的.