ILC_SignOutSp

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