sql2005存储过程有关问题

sql2005存储过程问题

SQL code

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 也是递增，是月份的递增

SQL code

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 @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 endend-- 执行@sqlexec(@sql)-- 打印@sqlprint @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 @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 endend-- 执行@sqlexec(@sql)-- 打印@sqlprint @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数据量小的话, 其实看不出差别的.