ALTER PROCEDURE [dbo].[PY_mylogo]
-- Add the parameters for the stored procedure here
AS
declare @orgid varchar(30),@DeviceID char(32),@GenerateTime varchar(100),@EndTime datetime,@CheckType char(32),@tiDeviceType varchar(10),@xh varchar(50),@CheckFrequence int
declare @id int
set @id=1
DECLARE authors_cursor CURSOR FOR (select orgid,DeviceID,GenerateTime,EndTime,CheckType,tiDeviceType,xh,CheckFrequence from tPeriodSetting where Datediff(HH,EndTime,getdate())>=CheckFrequence )
OPEN authors_cursor
FETCH NEXT FROM authors_cursor INTO @orgid,@DeviceID,@GenerateTime,@EndTime,@CheckType,@tiDeviceType,@xh,@CheckFrequence
WHILE @@FETCH_STATUS = 0
BEGIN
set @[email protected]+1
declare @devicechecktaskid varchar(100)
set @devicechecktaskid=ltrim((SELECT CONVERT(varchar(100), GETDATE(), 20))) + str(@id)
print @xh update tPeriodSetting set GenerateTime=(SELECT CONVERT(varchar(100), GETDATE(), 120)),EndTime=(SELECT DATEADD(HH, CheckFrequence, CONVERT(varchar(100), GETDATE(), 120))) where [email protected]
set @EndTime=(SELECT DATEADD(HH, @CheckFrequence, CONVERT(varchar(100), GETDATE(), 120)))
print @EndTime insert into deviceschecktask (devicechecktaskid,orgid,devicesbarcode,taskpriority,taskdispatchtime,EndTime,taskstatus,sourceoftask,tiDeviceType,checktype) values (@devicechecktaskid,@orgid,@DeviceID,'1',@GenerateTime,@EndTime,'待调度','周期任务',@tiDeviceType,@CheckType)
FETCH NEXT FROM authors_cursor INTO @orgid,@DeviceID,@GenerateTime,@EndTime,@CheckType,@tiDeviceType,@xh,@CheckFrequence
CLOSE authors_cursor
DEALLOCATE authors_cursor
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
END
以上设计应该会循环3次的，执行存储过程之后，出现内容 ：这里只打印出了一条数据的ID，并且我打印的EndTime未执行为什么？
70f041b3-714c-4e73-9ed5-ed06da64
(1 行受影响)
(1 行受影响)
(1 行受影响)
------解决方案--------------------
end位置错了
- SQL code
ALTER PROCEDURE [dbo].[PY_mylogo] -- Add the parameters for the stored procedure hereAS declare @orgid varchar(30),@DeviceID char(32),@GenerateTime varchar(100),@EndTime datetime,@CheckType char(32),@tiDeviceType varchar(10),@xh varchar(50),@CheckFrequence int declare @id int set @id=1 DECLARE authors_cursor CURSOR FOR (select orgid,DeviceID,GenerateTime,EndTime,CheckType,tiDeviceType,xh,CheckFrequence from tPeriodSetting where Datediff(HH,EndTime,getdate())>=CheckFrequence ) OPEN authors_cursor FETCH NEXT FROM authors_cursor INTO @orgid,@DeviceID,@GenerateTime,@EndTime,@CheckType,@tiDeviceType,@xh,@CheckFrequence WHILE @@FETCH_STATUS = 0 BEGIN set @[email protected]+1 declare @devicechecktaskid varchar(100) set @devicechecktaskid=ltrim((SELECT CONVERT(varchar(100), GETDATE(), 20))) + str(@id) print @xh update tPeriodSetting set GenerateTime=(SELECT CONVERT(varchar(100), GETDATE(), 120)) ,EndTime=(SELECT DATEADD(HH, CheckFrequence, CONVERT(varchar(100), GETDATE(), 120))) where [email protected] set @EndTime=(SELECT DATEADD(HH, @CheckFrequence, CONVERT(varchar(100), GETDATE(), 120))) print @EndTime insert into deviceschecktask ( devicechecktaskid,orgid,devicesbarcode,taskpriority,taskdispatchtime,EndTime,taskstatus,sourceoftask,tiDeviceType,checktype) values (@devicechecktaskid,@orgid,@DeviceID,'1',@GenerateTime,@EndTime,'待调度','周期任务',@tiDeviceType,@CheckType) FETCH NEXT FROM authors_cursor INTO @orgid,@DeviceID,@GenerateTime,@EndTime,@CheckType,@tiDeviceType,@xh,@CheckFrequence end CLOSE authors_cursor DEALLOCATE authors_cursor -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here