Stored Procedure Properties

Creation Options

Parameters

Total: 2 parameter(s)

SQL

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE         PROCEDURE forums_GetForumsForModerationByForumGroupId
(
	@ForumGroupId int,
	@UserName nvarchar(50)
	
)
AS

IF (0 = (SELECT ForumID FROM Moderators WHERE Username = @UserName))

	SELECT
		ForumID,
		ForumGroupId,
		Name,
		Description,
		DateCreated,
		DaysToView,
		Moderated,
		TotalPosts,
		TotalTopics = TotalThreads,
		MostRecentPostID,
		MostRecentThreadID,
		MostRecentPostDate,
		MostRecentPostAuthor,
		TotalPostsAwaitingModeration = (SELECT COUNT(*) FROM Posts P (nolock) WHERE P.ForumID = F.ForumID and P.Approved = 0),
		Active = 1,
		LastUserActivity = (SELECT LastActivity FROM ForumsRead WHERE Username = @UserName AND ForumID = F.ForumID),
		SortOrder,
		IsPrivate = 0
	FROM 
		Forums F (nolock)
	WHERE 
		Active = 1 AND
		ForumGroupId = @ForumGroupId AND
		(ForumID NOT IN (SELECT ForumID from PrivateForums) OR
		ForumID IN (SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @UserName))) AND
		(SELECT COUNT(*) FROM Posts P (nolock) WHERE P.ForumID = F.ForumID and P.Approved = 0) > 0
ELSE

	SELECT
		ForumID,
		ForumGroupId,
		Name,
		Description,
		DateCreated,
		DaysToView,
		Moderated,
		TotalPosts,
		TotalTopics = TotalThreads,
		MostRecentPostID,
		MostRecentThreadID,
		MostRecentPostDate,
		MostRecentPostAuthor,
		TotalPostsAwaitingModeration = (SELECT COUNT(*) FROM Posts P (nolock) WHERE P.ForumID = F.ForumID and P.Approved = 0),
		Active = 1,
		LastUserActivity = (SELECT LastActivity FROM ForumsRead WHERE Username = @UserName AND ForumID = F.ForumID),
		SortOrder,
		IsPrivate = 0
	FROM 
		Forums F (nolock)
	WHERE 
		Active = 1 AND
		ForumGroupId = @ForumGroupId AND
		(ForumID NOT IN (SELECT ForumID from PrivateForums) OR
		ForumID IN (SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @UserName))) AND
		ForumID IN (SELECT ForumID FROM Moderators WHERE Username = @UserName) AND
		(SELECT COUNT(*) FROM Posts P (nolock) WHERE P.ForumID = F.ForumID and P.Approved = 0) > 0







GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO