USE [EFNETSYS]
GO
/****** Object:  StoredProcedure [dbo].[SP_Dz]    Script Date: 05/26/2018 16:14:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_Dz]
(
    @注銷時間 int = 360
) 
AS
BEGIN
    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 當(dāng)前時間
    ,@Run_Time_ss - 
        ( Convert(int,substring(ZY002,18,2))
        + Convert(int,substring(ZY002,15,2)) * 60
        + Convert(int,substring(ZY002,12,2)) * 360) as 運(yùn)行時間
    ,ZZ004 from EFNETSYS.dbo.CRMZZ
left join EFNETSYS.dbo.CRMZY
on ZZ001 = ZY001 and ZZ004 = ZY004 ) as A
where 運(yùn)行時間 > @注銷時間
) as B
---------------------------------------------------------------
DECLARE  @i int = 1
        ,@rows int = (select COUNT(*) from #TempA)
        ,@ZZ004 nchar(20)
if @rows <> 0
begin
    while @i <= @rows
    begin
        select @ZZ004 = ZZ004 from #TempA where 序號 = @i
        delete from EFNETSYS..CRMZZ where ZZ004 = @ZZ004
        set @i = @i + 1
    end
end
drop table #TempA
---------------------------------------------------------------
End

GO

網(wǎng)站名稱：定時注銷電子簽核用戶
網(wǎng)站地址：http://jinyejixie.com/article14/pgsjde.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號、網(wǎng)站建設(shè)、建站公司、網(wǎng)站改版、網(wǎng)站策劃、網(wǎng)頁設(shè)計公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)