SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_system_UpdateSubscriptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_system_UpdateSubscriptions]
GO

CREATE PROCEDURE [dbo].cs_system_UpdateSubscriptions
(
	@RoleID uniqueidentifier,
	@SectionID int,
	@SettingsID int
)
AS
/*
	Remove users subscriptions based on provided role ID.
*/
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	DECLARE @UserID int
	DECLARE @HasAccess bit
	
	DECLARE users_cursor CURSOR FOR 
	SELECT UserID FROM cs_vw_UsersInRoles WHERE RoleId = @RoleID

	OPEN users_cursor

	FETCH NEXT FROM users_cursor 
	INTO @UserID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Get user's view & read access permissions status
		EXEC @HasAccess = cs_system_UserCanReadSection @UserID, @SectionID, @SettingsID
		
		-- If no access then clean up subscriptions
		IF @HasAccess = 0
		BEGIN
			-- Remove forum subscriptions
			EXEC cs_RemoveAllSectionTracking @UserID, @SectionID, @SettingsID

			-- Remove thread subscriptions
			EXEC cs_RemoveAllPostTracking @UserID, @SectionID, @SettingsID
		END

		FETCH NEXT FROM users_cursor
		INTO @UserID
	END

	CLOSE users_cursor
	DEALLOCATE users_cursor

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_system_UpdateSubscriptions] to public

