我写了个简单测试的存储,测试是可以的。但是写了个复杂的就不会自动执行了。存储过程绝对没问题,我执行了N次都没问题,具体是这样的create proc [dbo].[am]
as
declare @nowTime datetime --当前时间
declare @rand varchar(20) --随机取20-29的整数
set @nowTime=getdate()
create table #beforeDate --用于填充的临时表
(
ID int not null identity(1,1),
oneTime datetime,
twoTime datetime
)
declare @i int--当前日期数
set @i=datepart(day,@nowTime)
while @i>=1
begin
--update a1.dbo.AtdRecord set VerifyMode=VerifyMode+1
set @rand=CAST(RAND()*10 AS int)+20--随机取20-29的整数
insert into #beforeDate(oneTime,twoTime) values
(CONVERT(varchar(12),dateadd(d,-@i+1,@nowTime), 111 )+' '+'08:'+@rand,CONVERT(varchar(12),dateadd(d,-@i+1,@nowTime), 111 )+' '+'18:'+@rand+':16')
set @i=@i-1
end
--开始遍历临时表进行数据增改
SET XACT_ABORT ON
begin tran
declare @id int
declare @oneTime datetime
declare @twoTime datetime
declare pcurr cursor for
select id,oneTime,twoTime from #beforeDate
open pcurr
fetch next from pcurr into @id,@oneTime,@twoTime
while (@@fetch_status = 0)
begin
declare @date varchar(50) --日期
declare @row varchar(50) --行数
set @date=datepart(day,@oneTime)
declare @inside datetime --循环体内的时间
declare @morning bit --早上是否有有效数据
declare @night bit --晚上是否有有效时间
set @morning=0
set @night=0
declare pcurrInside cursor for --循环体内的游标
select RecDate+' '+RecTime from a1.dbo.AtdRecord as zy where
datepart(year,zy.RecDate)=datepart(year,@nowTime)
and datepart(month,zy.RecDate)=datepart(month,@nowTime)
and datepart(day,zy.RecDate)=@date
and zy.CardNo='03003'
open pcurrInside
fetch next from pcurrInside into @inside
while (@@fetch_status = 0)
begin
if @inside>convert(varchar(120),convert(datetime,convert(varchar(12),datepart(year,@nowTime))+'-'+convert(varchar(12),datepart(month,@nowTime))+'-'+convert(varchar(12),@date)+' '+'18:00'),120) --是否包含有效的晚上时间
begin
set @night=1
end
declare @morningdate datetime
set @morningdate=convert(varchar(120),convert(datetime,convert(varchar(12),datepart(year,@nowTime))+'-'+convert(varchar(12),datepart(month,@nowTime))+'-'+convert(varchar(12),@date)+' '+'08:30'),120) --是否包含有效的早上时间
if @inside<=@morningdate
begin
set @morning=1
end
fetch next from pcurrInside into @inside
end
close pcurrInside
deallocate pcurrInside
if(@morning=0)
begin
insert into a1.dbo.AtdRecord (EmplID,CardNo,RecDate,RecTime,IsAuto,VerifyMode,EquNo,InOutType,OperId,
OperDate,Checker,PersonalRec) values
('00000010','03003',CONVERT(varchar(100),@oneTime,23),left(CONVERT(varchar(100),@oneTime,108),5),
2,1,'001',2,'sa',CONVERT(varchar(100),@twoTime,120),
'sa',0)
end
fetch next from pcurr into @id,@oneTime,@twoTime
end
close pcurr
deallocate pcurr
commit tran这个勾选我也打上了USE MASTER
EXEC SP_PROCOPTION am, 'STARTUP', 'ON' 也启动了为何不自动执行啊
as
declare @nowTime datetime --当前时间
declare @rand varchar(20) --随机取20-29的整数
set @nowTime=getdate()
create table #beforeDate --用于填充的临时表
(
ID int not null identity(1,1),
oneTime datetime,
twoTime datetime
)
declare @i int--当前日期数
set @i=datepart(day,@nowTime)
while @i>=1
begin
--update a1.dbo.AtdRecord set VerifyMode=VerifyMode+1
set @rand=CAST(RAND()*10 AS int)+20--随机取20-29的整数
insert into #beforeDate(oneTime,twoTime) values
(CONVERT(varchar(12),dateadd(d,-@i+1,@nowTime), 111 )+' '+'08:'+@rand,CONVERT(varchar(12),dateadd(d,-@i+1,@nowTime), 111 )+' '+'18:'+@rand+':16')
set @i=@i-1
end
--开始遍历临时表进行数据增改
SET XACT_ABORT ON
begin tran
declare @id int
declare @oneTime datetime
declare @twoTime datetime
declare pcurr cursor for
select id,oneTime,twoTime from #beforeDate
open pcurr
fetch next from pcurr into @id,@oneTime,@twoTime
while (@@fetch_status = 0)
begin
declare @date varchar(50) --日期
declare @row varchar(50) --行数
set @date=datepart(day,@oneTime)
declare @inside datetime --循环体内的时间
declare @morning bit --早上是否有有效数据
declare @night bit --晚上是否有有效时间
set @morning=0
set @night=0
declare pcurrInside cursor for --循环体内的游标
select RecDate+' '+RecTime from a1.dbo.AtdRecord as zy where
datepart(year,zy.RecDate)=datepart(year,@nowTime)
and datepart(month,zy.RecDate)=datepart(month,@nowTime)
and datepart(day,zy.RecDate)=@date
and zy.CardNo='03003'
open pcurrInside
fetch next from pcurrInside into @inside
while (@@fetch_status = 0)
begin
if @inside>convert(varchar(120),convert(datetime,convert(varchar(12),datepart(year,@nowTime))+'-'+convert(varchar(12),datepart(month,@nowTime))+'-'+convert(varchar(12),@date)+' '+'18:00'),120) --是否包含有效的晚上时间
begin
set @night=1
end
declare @morningdate datetime
set @morningdate=convert(varchar(120),convert(datetime,convert(varchar(12),datepart(year,@nowTime))+'-'+convert(varchar(12),datepart(month,@nowTime))+'-'+convert(varchar(12),@date)+' '+'08:30'),120) --是否包含有效的早上时间
if @inside<=@morningdate
begin
set @morning=1
end
fetch next from pcurrInside into @inside
end
close pcurrInside
deallocate pcurrInside
if(@morning=0)
begin
insert into a1.dbo.AtdRecord (EmplID,CardNo,RecDate,RecTime,IsAuto,VerifyMode,EquNo,InOutType,OperId,
OperDate,Checker,PersonalRec) values
('00000010','03003',CONVERT(varchar(100),@oneTime,23),left(CONVERT(varchar(100),@oneTime,108),5),
2,1,'001',2,'sa',CONVERT(varchar(100),@twoTime,120),
'sa',0)
end
fetch next from pcurr into @id,@oneTime,@twoTime
end
close pcurr
deallocate pcurr
commit tran这个勾选我也打上了USE MASTER
EXEC SP_PROCOPTION am, 'STARTUP', 'ON' 也启动了为何不自动执行啊
, [ @OptionName = ] 'option'
, [ @OptionValue = ] 'value'
备注
启动过程必须位于 master 数据库中,并且不能包含 INPUT 或 OUTPUT 参数。启动时恢复了 master 数据库后,即开始执行存储过程。 参数
[ @ProcName = ] 'procedure'
为其设置选项的过程的名称。 procedure 的数据类型为 nvarchar(776),无默认值。[ @OptionName = ] 'option'
要设置的选项的名称。option 的唯一值为 startup。[ @OptionValue = ] 'value'
指示是将选项设置为开启(true 或 on)还是关闭(false 或 off)。value 的数据类型为 varchar(12),无默认值。
存储过程没错的,我测试过。也是放在MASTER数据库里的。也没有输入和输出参数
GO
CREATE TABLE TB(ID INT)GO
CREATE PROC P
AS
SELECT * FROM TB
EXEC SP_PROCOPTION P, 'STARTUP', 'ON'