cs_users_get.prc

解压即可使用

	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND (UserName LIKE @UsernameFilter OR Email LIKE @UsernameFilter) ORDER BY LastActivityDate DESC
ELSE IF @SortBy = 3 AND @SortOrder = 0
    IF @UsernameFilter IS NULL
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  ORDER BY LastActivityDate
    ELSE
	IF @FilterIncludesEmailAddress = 0
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND UserName LIKE @UsernameFilter ORDER BY LastActivityDate
	ELSE
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND (UserName LIKE @UsernameFilter OR Email LIKE @UsernameFilter) ORDER BY LastActivityDate

-- Sort by TotalPosts
IF @SortBy = 4 AND @SortOrder = 1
    IF @UsernameFilter IS NULL
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  ORDER BY TotalPosts DESC
    ELSE
	IF @FilterIncludesEmailAddress = 0
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND UserName LIKE @UsernameFilter ORDER BY TotalPosts DESC
	ELSE
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND (UserName LIKE @UsernameFilter OR Email LIKE @UsernameFilter) ORDER BY TotalPosts DESC
ELSE IF @SortBy = 4 AND @SortOrder = 0
    IF @UsernameFilter IS NULL
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  ORDER BY TotalPosts
    ELSE
	IF @FilterIncludesEmailAddress = 0
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND UserName LIKE @UsernameFilter ORDER BY TotalPosts
	ELSE
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND (UserName LIKE @UsernameFilter OR Email LIKE @UsernameFilter) ORDER BY TotalPosts


IF @SortBy = 5 AND @SortOrder = 0
    IF @UsernameFilter IS NULL
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  ORDER BY Email
    ELSE
	IF @FilterIncludesEmailAddress = 0
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND UserName LIKE @UsernameFilter ORDER BY Email
	ELSE
	    INSERT INTO #PageIndexForUsers (UserID)	    
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND (UserName LIKE @UsernameFilter OR Email LIKE @UsernameFilter) ORDER BY Email
ELSE IF @SortBy = 5 AND @SortOrder = 1
    IF @UsernameFilter IS NULL
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  ORDER BY Email DESC
    ELSE
	IF @FilterIncludesEmailAddress = 0
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND UserName LIKE @UsernameFilter ORDER BY Email DESC
	ELSE
	    INSERT INTO #PageIndexForUsers (UserID)
	    SELECT P.cs_UserID FROM cs_vw_Users_FullUser P WHERE SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1)  AND (UserName LIKE @UsernameFilter OR Email LIKE @UsernameFilter) ORDER BY Email DESC


-- Get the user details
IF @ReturnModerationCounters = 1
	SELECT
		U.*,
		ApprovePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 1 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		EditPost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 2 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		MovePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 3 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		DeletePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 4 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		LockPost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 5 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		UnlockPost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 6 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		MergePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 7 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		SplitPost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 8 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		EditUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 9 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		UnmoderateUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 10 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		ModerateUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 11 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		BanUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 12 AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		UnbanUser = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 13  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		ResetPassword = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 14  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		ChangePassword = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 15  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		PostIsAnnouncement = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 16  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		PostIsNotAnnoucement = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 17  AND UserID = U.cs_UserID AND SettingsID = @SettingsID),
		UnApprovePost = (SELECT COUNT(*) FROM cs_ModerationAudit WHERE ModerationAction = 18  AND UserID = U.cs_UserID AND SettingsID = @SettingsID)	
		-- (we do not see to be using this value),IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID)
	FROM 
		cs_vw_Users_FullUser U (nolock),
		#PageIndexForUsers
	WHERE 
		U.cs_UserID = #PageIndexForUsers.UserID AND
		#PageIndexForUsers.IndexID > @PageLowerBound AND
		#PageIndexForUsers.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
	ORDER BY
		#PageIndexForUsers.IndexID
ELSE
	SELECT
		U.*
		-- (we do not see to be using this value),IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID)
	FROM 
		cs_vw_Users_FullUser U (nolock),
		#PageIndexForUsers
	WHERE 
		U.cs_UserID = #PageIndexForUsers.UserID AND
		#PageIndexForUsers.IndexID > @PageLowerBound AND
		#PageIndexForUsers.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
	ORDER BY
		#PageIndexForUsers.IndexID

END

-- Return the record count if necessary

IF (@ReturnRecordCount = 1)
  SELECT @TotalUsers
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_users_Get]  TO [public]
GO