sql2005存储过程有关问题

select * from Journal where AdAgStart < 45 and AdAgEnd >=45 and AdMon=12select * from Journal where AdAgStart < 46 and AdAgEnd >=46 and AdMon=1select * from Journal where AdAgStart < 47 and AdAgEnd >=47 and AdMon=2select * from Journal where AdAgStart < 48 and AdAgEnd >=48 and AdMon=3select * from Journal where AdAgStart < 49 and AdAgEnd >=49 and AdMon=4select * from Journal where AdAgStart < 50 and AdAgEnd >=50 and AdMon=545,46,47,48,49,50是递增的。知道45知道循环6次得出来的结果就是45,46,47,48,49,5012,1,2,3,4,5 也是递增，是月份的递增

create proc pr_test@AdAg intasdeclare @m intset @m = month(getdate())declare @i intset @i = 0while @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 + 1endgo-调用exec pr_test 45
------解决方案--------------------
探讨


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

------解决方案--------------------
用动态SQL实现,
SQL code
declare @x int,@y int,@m int,@sql varchar(6000)-- 用户输入的变量select @x=45,@y=6select @[email protected][email protected],@m=month(getdate()),@sql=''while(@x<[email protected])begin  select @[email protected]+'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 @[email protected]+1,@m=case when @m+1=13 then 1 else @m+1 endend-- [email protected]exec(@sql)-- [email protected]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=6select @[email protected][email protected],@m=month(getdate()),@sql=''while(@x<[email protected])begin  select @[email protected]+'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 @[email protected] then ' ' else ' union all ' end  select @[email protected]+1,@m=case when @m+1=13 then 1 else @m+1 endend-- [email protected]exec(@sql)-- [email protected]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数据量小的话, 其实看不出差别的.