日期:2014-05-18  浏览次数:20605 次

Sql语句 循环调用存储过程 设置1秒执行一次!
一个日结的存储过程P_Wms_StockDailyState 参数是格式是'yyyy-MM-dd'

这个存储过程必须一天结算一次!比如在每天的几点几时几分执行这个存储过程!

现在呢 想把之前的也执行掉!意思就是  

我从 2012-5-1 开始 执行到 今天之前的2012-7-23 设置1秒执行一次!

当执行到今天之前后停止! 后面 就是一天的某个时间 点执行一次!  



------解决方案--------------------
你是说参数一秒一次还是说一秒执行一次存储过程,参数是每天的日期?

如果是后者,为什么需要限制时间呢?如果1秒内执行不完如何处理?
直接循环执行,执行好一天后,日期加1再执行第二次不就行了?这样一直执行到2012-7-23时停止
------解决方案--------------------
SQL code

declare @dt varchar(30),@today varchar(30)
select @dt ='2012-05-01',@today=left(convert(varchar(30),getdate(),120),10)

while @dt<@today
begin
    exec P_Wms_StockDailyState @dt
    set @dt=left(convert(varchar(30),DATEADD(day,1,@dt),120),10)
    waitfor delay '00:00:01'
end
--是这个意思么

------解决方案--------------------
SQL code

--测试结果:
declare @dt varchar(30),@today varchar(30)
select @dt ='2012-05-01',@today=left(convert(varchar(30),getdate(),120),10)

while @dt<@today
begin
    exec P_Wms_StockDailyState @dt
    set @dt=left(convert(varchar(30),DATEADD(day,1,@dt),120),10)
    waitfor delay '00:00:01'
end
--输出:
exec P_Wms_StockDailyState '2012-05-01'
exec P_Wms_StockDailyState '2012-05-02'
exec P_Wms_StockDailyState '2012-05-03'
exec P_Wms_StockDailyState '2012-05-04'
exec P_Wms_StockDailyState '2012-05-05'
exec P_Wms_StockDailyState '2012-05-06'
exec P_Wms_StockDailyState '2012-05-07'
exec P_Wms_StockDailyState '2012-05-08'
exec P_Wms_StockDailyState '2012-05-09'
exec P_Wms_StockDailyState '2012-05-10'
exec P_Wms_StockDailyState '2012-05-11'
exec P_Wms_StockDailyState '2012-05-12'
exec P_Wms_StockDailyState '2012-05-13'
exec P_Wms_StockDailyState '2012-05-14'
exec P_Wms_StockDailyState '2012-05-15'
exec P_Wms_StockDailyState '2012-05-16'
exec P_Wms_StockDailyState '2012-05-17'
exec P_Wms_StockDailyState '2012-05-18'
exec P_Wms_StockDailyState '2012-05-19'
exec P_Wms_StockDailyState '2012-05-20'
exec P_Wms_StockDailyState '2012-05-21'
exec P_Wms_StockDailyState '2012-05-22'
exec P_Wms_StockDailyState '2012-05-23'
exec P_Wms_StockDailyState '2012-05-24'
exec P_Wms_StockDailyState '2012-05-25'
exec P_Wms_StockDailyState '2012-05-26'
exec P_Wms_StockDailyState '2012-05-27'
exec P_Wms_StockDailyState '2012-05-28'
exec P_Wms_StockDailyState '2012-05-29'
exec P_Wms_StockDailyState '2012-05-30'
exec P_Wms_StockDailyState '2012-05-31'
exec P_Wms_StockDailyState '2012-06-01'
exec P_Wms_StockDailyState '2012-06-02'
exec P_Wms_StockDailyState '2012-06-03'
exec P_Wms_StockDailyState '2012-06-04'
exec P_Wms_StockDailyState '2012-06-05'
exec P_Wms_StockDailyState '2012-06-06'
exec P_Wms_StockDailyState '2012-06-07'
exec P_Wms_StockDailyState '2012-06-08'
exec P_Wms_StockDailyState '2012-06-09'
exec P_Wms_StockDailyState '2012-06-10'
exec P_Wms_StockDailyState '2012-06-11'
exec P_Wms_StockDailyState '2012-06-12'
exec P_Wms_StockDailyState '2012-06-13'
exec P_Wms_StockDailyState '2012-06-14'
exec P_Wms_StockDailyState '2012-06-15'
exec P_Wms_StockDailyState '2012-06-16'
exec P_Wms_StockDailyState '2012-06-17'
exec P_Wms_StockDailyState '2012-06-18'
exec P_Wms_StockDailyState '2012-06-19'
exec P_Wms_StockDailyState '2012-06-20'
exec P_Wms_StockDailyState '2012-06-21'
exec P_Wms_StockDailyState '2012-06-22'
exec P_Wms_StockDailyState '2012-06-23'
exec P_Wms_StockDailyState '2012-06-24'
exec P_Wms_StockDailyState '2012-06-25'
exec P_Wms_StockDailyState '2012-06-26'
exec P_Wms_StockDailyState '2012-06-27'
exec P_Wms_StockDailyState '2012-06-28'
exec P_Wms_StockDailyState '2012-06-29'
exec P_Wms_StockDailyState '2012-06-30'
exec P_Wms_StockDailyState '2012-07-01'
exec P_Wms_StockDailyState '2012-07-02'
exec P_Wms_StockDailyState '2012-07-03'
exec P_Wms_StockDailyState '2012-07-04'
exec P_Wms_StockDailyState '2012-07-05'
exec P_Wms_StockDailyState '2012-07-06'
exec P_Wms_StockDailyState '2012-07-07'
exec P_Wms_S