定时注销电子签核用户

USE [EFNETSYS]GO/****** Object:  StoredProcedure [dbo].[SP_Dz]    Script Date: 05/26/2018 16:14:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[SP_Dz](    @注销时间 int = 360) ASBEGIN    SET NOCOUNT ON;      --------------------------------------------------------------if object_id('tempdb..#TempA') is not null drop table #TempA; -------------------------------------------------------------- DECLARE @Time nchar(20) = replace(Convert(nchar(20),GETDATE(),120),'-','/') DECLARE @Run_Time_ss int , @XUHAO  varchar(8) set @Run_Time_ss =   Convert(int,substring(@Time,18,2))                    + Convert(int,substring(@Time,15,2)) * 60                    + Convert(int,substring(@Time,12,2)) * 360 select * into #TempA from (select    ROW_NUMBER() OVER (ORDER BY @XUHAO  ASC) AS '序号'   ,* from (select     ZZ001 as 登录者    ,ZY002 as 起始时间    ,@Time as 当前时间    ,@Run_Time_ss -         ( Convert(int,substring(ZY002,18,2))        + Convert(int,substring(ZY002,15,2)) * 60        + Convert(int,substring(ZY002,12,2)) * 360) as 运行时间    ,ZZ004 from EFNETSYS.dbo.CRMZZleft join EFNETSYS.dbo.CRMZYon ZZ001 = ZY001 and ZZ004 = ZY004 ) as Awhere 运行时间 > @注销时间) as B---------------------------------------------------------------DECLARE  @i int = 1        ,@rows int = (select COUNT(*) from #TempA)        ,@ZZ004 nchar(20)if @rows <> 0begin    while @i <= @rows    begin        select @ZZ004 = ZZ004 from #TempA where 序号 = @i        delete from EFNETSYS..CRMZZ where ZZ004 = @ZZ004        set @i = @i + 1    endenddrop table #TempA---------------------------------------------------------------EndGO