Pasted

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ILC_SignOutSp]') AND TYPE IN (N'P', N'PC'))
BEGIN
 
DROP PROCEDURE [dbo].[ILC_SignOutSp]
 
END
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- 02-SEP-2020  sign users out if they have been logged in for more than 12 hours/run hourly
-- =============================================
CREATE PROCEDURE [dbo].[ILC_SignOutSp] (
    @HOUR ILC_IntegerType = 12
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    DECLARE @T TABLE (ConnectionID UNIQUEIDENTIFIER)
 
    INSERT @T(ConnectionID)
    SELECT ci.ConnectionID
    FROM dbo.connectioninformation ci
    WHERE (
    ci.SessionType = 1 
    AND DATEADD(HOUR,DATEDIFF(HOUR,dbo.GetSiteNow(), GETDATE()), ci.CreateDate)<DATEADD(HOUR,-1*@HOUR,GETDATE())
    )
    OR (
    ci.SessionType = 0 
    AND ci.CreationContext IS NULL
    AND ci.UserName NOT LIKE '$%' --$MGEvent
    AND DATEADD(HOUR,DATEDIFF(HOUR,dbo.GetSiteNow(), GETDATE()), ci.CreateDate)<DATEADD(HOUR,-2,GETDATE())
    )
 
    DECLARE @SessionID UNIQUEIDENTIFIER
    SELECT TOP 1 @SessionID = ConnectionID 
    FROM @T
    ORDER BY ConnectionID
 
    WHILE @SessionID IS NOT NULL
    BEGIN
        EXEC closesessionsp @SessionID
 
        DELETE @T
        WHERE ConnectionID=@SessionID
 
        SET @SessionID = NULL
 
        SELECT TOP 1 @SessionID = ConnectionID 
        FROM @T
        ORDER BY ConnectionID
    END
END
 
GO