SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


create  procedure [dbo].cs_UserHasPostsAwaitingModeration
(
	@UserName nvarchar(50),
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
	-- Can the user moderate all forums?
	IF EXISTS(SELECT NULL FROM Moderators (nolock) WHERE UserName=@UserName AND SectionID=0)

		-- return ALL posts awaiting moderation
		IF EXISTS(SELECT TOP 1 PostID FROM Posts P (nolock) INNER JOIN Forums F (nolock) ON F.SectionID = P.SectionID WHERE Approved = 0 and SettingsID = @SettingsID)
		  SELECT 1
		ELSE
		  SELECT 0
	ELSE
		-- return only those posts in the forum this user can moderate
		IF EXISTS (SELECT TOP 1 PostID FROM Posts P (nolock) INNER JOIN Forums F (nolock) ON F.SectionID = P.SectionID WHERE Approved = 0 AND P.SectionID IN (SELECT SectionID FROM Moderators (nolock) WHERE UserName=@UserName and SettingsID = @SettingsID)  and F.SettingsID = @SettingsID)
		  SELECT 1
		ELSE
		  SELECT 0
	
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_UserHasPostsAwaitingModeration] to public
go