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