cs_users_get.prc

解压即可使用

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




--cs_users_Get 0, 10, 0, 0, null, 0, 1, 0, 0, 1000
CREATE          PROCEDURE [dbo].cs_users_Get
(
	@PageIndex int,
	@PageSize int,
	@SortBy int = 0,
	@SortOrder int = 0,
	@UsernameFilter nvarchar(128),
	@FilterIncludesEmailAddress bit = 0,
	@UserAccountStatus smallint = 1,
	@ReturnRecordCount bit = 0,
	@IncludeHiddenUsers bit = 0,
	@ReturnModerationCounters bit = 0,
	@SettingsID int
)
AS

SET Transaction Isolation Level Read UNCOMMITTED

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)
  IF ((@UsernameFilter IS NULL) OR (@UsernameFilter = ''))
    SET @TotalUsers = (SELECT count(cs_UserID) FROM cs_vw_Users_FullUser where SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1))
  ELSE
    IF (@FilterIncludesEmailAddress = 0)
      SET @TotalUsers = (SELECT count(cs_UserID) FROM cs_vw_Users_FullUser where SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1) AND UserName LIKE @UsernameFilter)
    ELSE
      SET @TotalUsers = (SELECT count(cs_UserID) FROM cs_vw_Users_FullUser where SettingsID = @SettingsID and  (cs_UserAccountStatus = @UserAccountStatus OR @UserAccountStatus = 9999) AND (EnableDisplayInMemberList = 1 or @IncludeHiddenUsers = 1) AND (UserName LIKE @UsernameFilter OR Email LIKE @UsernameFilter))


-- Special case depending on what the user wants and how they want it ordered by
-- *************************************
--Select * FROM cs_vw_Users_FullUser
-- Sort by Date Joined
IF @SortBy = 0 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 CreateDate
    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 CreateDate
	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 CreateDate
ELSE IF @SortBy = 0 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 CreateDate 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 CreateDate 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 CreateDate DESC

-- Sort by username
IF @SortBy = 1 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 UserName
    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 UserName
	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 UserName
ELSE IF @SortBy = 1 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 UserName 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 UserName 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 UserName DESC

-- Sort by Last Active
IF @SortBy = 3 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 LastActivityDate 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 LastActivityDate DESC
	ELSE
	    INSERT INTO #PageIndexForUsers (UserID)