I want to create a while loop in sql server which starts execution from January, 2014 and executes until current month of current year.
我想在sql server中创建一个while循环,它从2014年1月开始执行,并执行到当前年的当前月份。
This is my code right now,
这是我现在的代码,
declare @month int
set @month = 1
while @month <= 12
begin
truncate table #temp_Products
insert into #temp_Products
exec sp_Products_Count_Monthly @month, @year
insert into temp_Products_monthly
select @month as Created_Month, @year as Created_Year, * from #temp_Products
set @month = @month + 1
end
I know loops are not a good practice in SQL, but I do not know how to execute a stored procedure for each month and store the result set in a table.
我知道循环在SQL中不是一个好习惯,但我不知道如何为每个月执行存储过程并将结果集存储在表中。
So I want to get the results from January 2014 until current month of 2015.
所以我希望从2014年1月到2015年的当月获得结果。
How can I do this?
我怎样才能做到这一点?
1 个解决方案
#1
1
Loops aren't the best performing solution, at least in all cases, but if you can divide your work into smaller pieces (but not into row-by-row processing) it might actually be better than trying to do a really complex thing to handle everything at once.
循环不是性能最佳的解决方案,至少在所有情况下都是如此,但是如果你可以将你的工作分成更小的部分(但不是逐行处理),那么实际上可能比尝试做一个非常复杂的事情更好。一次处理所有事情。
For this you could just use a simple date variable for the loop:
为此,您可以只为循环使用一个简单的日期变量:
declare @tmpdate date, @year int, @month int
set @tmpdate = '20140101'
while @tmpdate < getdate() begin
set @month = month(@tmpdate)
set @year = year(@tmpdate)
truncate table #temp_Products
insert into #temp_Products
exec sp_Products_Count_Monthly @month, @year
insert into temp_Products_monthly
select @month as Created_Month, @year as Created_Year, * from #temp_Products
set @tmpdate = dateadd(month, 1, @tmpdate)
end
If this is executed a lot, then you probably should try to rewrite the whole logic, so that you wouldn't need to call a separate procedure for each month, but actually fetch the whole data in single batch.
如果执行了很多,那么你可能应该尝试重写整个逻辑,这样你就不需要为每个月调用一个单独的过程,而是实际上一次性获取整个数据。
#1
1
Loops aren't the best performing solution, at least in all cases, but if you can divide your work into smaller pieces (but not into row-by-row processing) it might actually be better than trying to do a really complex thing to handle everything at once.
循环不是性能最佳的解决方案,至少在所有情况下都是如此,但是如果你可以将你的工作分成更小的部分(但不是逐行处理),那么实际上可能比尝试做一个非常复杂的事情更好。一次处理所有事情。
For this you could just use a simple date variable for the loop:
为此,您可以只为循环使用一个简单的日期变量:
declare @tmpdate date, @year int, @month int
set @tmpdate = '20140101'
while @tmpdate < getdate() begin
set @month = month(@tmpdate)
set @year = year(@tmpdate)
truncate table #temp_Products
insert into #temp_Products
exec sp_Products_Count_Monthly @month, @year
insert into temp_Products_monthly
select @month as Created_Month, @year as Created_Year, * from #temp_Products
set @tmpdate = dateadd(month, 1, @tmpdate)
end
If this is executed a lot, then you probably should try to rewrite the whole logic, so that you wouldn't need to call a separate procedure for each month, but actually fetch the whole data in single batch.
如果执行了很多,那么你可能应该尝试重写整个逻辑,这样你就不需要为每个月调用一个单独的过程,而是实际上一次性获取整个数据。