为什么这存储过程没循环呀？新手小白，求大神，大仙指教、、、
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 @id=@id+1
declare @devicechecktaskid varchar(100)
set @devicechecktaskid=ltrim((SELECT CONVERT(varchar(100), GETDATE(), 20))) + str(@id)
print @xhupdate tPeriodSetting set GenerateTime=(SELECT CONVERT(varchar(100), GETDATE(), 120)),EndTime=(SELECT DATEADD(HH, CheckFrequence, CONVERT(varchar(100), GETDATE(), 120))) where xh=@xh
set @EndTime=(SELECT DATEADD(HH, @CheckFrequence, CONVERT(varchar(100), GETDATE(), 120)))
print @EndTimeinsert 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位置错了
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 @id=@id+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 xh=@xh 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
[解决办法]
ALTER PROCEDURE [dbo].[PY_mylogo] -- Add the parameters for the stored procedure hereASdeclare @orgid varchar(30), @DeviceID char(32), @GenerateTime varchar(100), @EndTime datetime, @CheckType char(32), @tiDeviceType varchar(10), @xh varchar(50), @CheckFrequence intdeclare @id int set @id=1DECLARE 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, @CheckFrequenceWHILE @@FETCH_STATUS = 0BEGIN set @id=@id+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 xh=@xh 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,@CheckFrequenceEND 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--TRY
[解决办法]
