cs_users_usersinrole_get.prc

community server 源码

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].cs_users_UsersInRole_Get
(
	@PageIndex int,
	@PageSize int,
	@SortBy int = 0,
	@SortOrder int = 0,
	@RoleID uniqueidentifier,
	@UserAccountStatus smallint = 1,
	@ReturnRecordCount bit = 0,
	@SettingsID int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalUsers int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Create a temp table to store the select results
CREATE TABLE #PageIndexForUsers 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	UserID int
)	

-- Do we need to return a record count?
-- *************************************
IF (@ReturnRecordCount = 1)
    SET @TotalUsers = (SELECT count(*) FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserId = U.UserId AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleId = @RoleID and U.SettingsID = @SettingsID)

-- Special case depending on what the user wants and how they want it ordered by
-- *************************************

-- Sort by Date Joined
IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndexForUsers (UserID)
    SELECT U.cs_UserID FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserID = U.UserID AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID and U.SettingsID = @SettingsID ORDER BY CreateDate
ELSE IF @SortBy = 0 AND @SortOrder = 0
    INSERT INTO #PageIndexForUsers (UserID)
    SELECT U.cs_UserID FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserID = U.UserID AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID and U.SettingsID = @SettingsID  ORDER BY CreateDate DESC

-- Sort by username
IF @SortBy = 1 AND @SortOrder = 1
	INSERT INTO #PageIndexForUsers (UserID)
    SELECT U.cs_UserID FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserID = U.UserID AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID and U.SettingsID = @SettingsID  ORDER BY UserName DESC
ELSE IF @SortBy = 1 AND @SortOrder = 0
	INSERT INTO #PageIndexForUsers (UserID)
    SELECT U.cs_UserID FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserID = U.UserID AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID and U.SettingsID = @SettingsID  ORDER BY UserName

-- Sort by Last Active
IF @SortBy = 3 AND @SortOrder = 1
    INSERT INTO #PageIndexForUsers (UserID)
    SELECT U.cs_UserID FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserID = U.UserID AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID and U.SettingsID = @SettingsID  ORDER BY cs_LastActivity DESC
ELSE IF @SortBy = 3 AND @SortOrder = 0
    INSERT INTO #PageIndexForUsers (UserID)
    SELECT U.cs_UserID FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserID = U.UserID AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID and U.SettingsID = @SettingsID  ORDER BY cs_LastActivity

-- Sort by TotalPosts
IF @SortBy = 4 AND @SortOrder = 1
    INSERT INTO #PageIndexForUsers (UserID)
    SELECT U.cs_UserID FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserID = U.UserID AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID and U.SettingsID = @SettingsID  ORDER BY TotalPosts DESC
ELSE IF @SortBy = 4 AND @SortOrder = 0
    INSERT INTO #PageIndexForUsers (UserID)
    SELECT U.cs_UserID FROM cs_vw_Users_FullUser U, aspnet_UsersInRoles R WHERE R.UserID = U.UserID AND U.cs_UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID and U.SettingsID = @SettingsID  ORDER BY TotalPosts

-- Get the user details
SELECT
	*,
	IsModerator = (SELECT Count(*) FROM cs_Moderators WHERE UserID = U.cs_UserID and SettingsID = @SettingsID)
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_UsersInRole_Get] to public
go