ALTER PROCEDURE dbo.aspnet_Profile_GetProfiles
@ApplicationName NVARCHAR(256),
@ProfileAuthOptions INT,
@PageIndex INT,
@PageSize INT,
@TimeZoneAdjustment INT,
@UserNameToMatch NVARCHAR(256) = NULL,
@InactiveSinceDate DATETIME = NULL
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
-----如果@ApplicationId为空直接返回,好象少了 SELECT 0 这一步,所以也就没用 BEGIN 和 END
IF (@ApplicationId IS NULL)
RETURN
IF ((NOT(@InactiveSinceDate IS NULL)) AND (@InactiveSinceDate > CONVERT(DATETIME, '17540101', 112)) AND (@InactiveSinceDate < CONVERT(DATETIME, '99980101', 112)))
SELECT @InactiveSinceDate = DATEADD(n, -@TimeZoneAdjustment, @InactiveSinceDate)
--如果@InactiveSinceDate不为空且在一定范围内,则为@InactiveSinceDate设置新的值
-- Set the page bounds
---设置页面绑定
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
DECLARE @TotalRecords INT
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
/*即显示数据的时候只显示从@PageLowerBound开始到@PageUpperBound的记录数*/
-- Create a temp table TO store the select results
--创建一个临时表来保存数据,自动增长的列是为了分页而设计的
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId UNIQUEIDENTIFIER
)
-- Insert into our temp table
---为临时表插入数据
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p
WHERE ApplicationId = @ApplicationId
AND u.UserId = p.UserId
AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
AND ( (@ProfileAuthOptions = 2)
OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
)
AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
ORDER BY UserName
/*现在没搞清楚的是这些条件,不明白条件的意思*/
SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
/*DATALENGTH 返回任何表达式所占用的字节数。*/
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
SELECT COUNT(*)
FROM #PageIndexForUsers ---计算临时表的记录数
DROP TABLE #PageIndexForUsers --删除临时表
/*既然不用话为什么还要弄一个分页的出来,不知道干什么用来着,不过前面有这个参数那就肯定有用了*/
END
@ApplicationName NVARCHAR(256),
@ProfileAuthOptions INT,
@PageIndex INT,
@PageSize INT,
@TimeZoneAdjustment INT,
@UserNameToMatch NVARCHAR(256) = NULL,
@InactiveSinceDate DATETIME = NULL
AS
BEGIN
DECLARE @ApplicationId UNIQUEIDENTIFIER
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
-----如果@ApplicationId为空直接返回,好象少了 SELECT 0 这一步,所以也就没用 BEGIN 和 END
IF (@ApplicationId IS NULL)
RETURN
IF ((NOT(@InactiveSinceDate IS NULL)) AND (@InactiveSinceDate > CONVERT(DATETIME, '17540101', 112)) AND (@InactiveSinceDate < CONVERT(DATETIME, '99980101', 112)))
SELECT @InactiveSinceDate = DATEADD(n, -@TimeZoneAdjustment, @InactiveSinceDate)
--如果@InactiveSinceDate不为空且在一定范围内,则为@InactiveSinceDate设置新的值
-- Set the page bounds
---设置页面绑定
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
DECLARE @TotalRecords INT
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
/*即显示数据的时候只显示从@PageLowerBound开始到@PageUpperBound的记录数*/
-- Create a temp table TO store the select results
--创建一个临时表来保存数据,自动增长的列是为了分页而设计的
CREATE TABLE #PageIndexForUsers
(
IndexId int IDENTITY (0, 1) NOT NULL,
UserId UNIQUEIDENTIFIER
)
-- Insert into our temp table
---为临时表插入数据
INSERT INTO #PageIndexForUsers (UserId)
SELECT u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p
WHERE ApplicationId = @ApplicationId
AND u.UserId = p.UserId
AND (@InactiveSinceDate IS NULL OR LastActivityDate <= @InactiveSinceDate)
AND ( (@ProfileAuthOptions = 2)
OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
)
AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))
ORDER BY UserName
/*现在没搞清楚的是这些条件,不明白条件的意思*/
SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate,
DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary)
/*DATALENGTH 返回任何表达式所占用的字节数。*/
FROM dbo.aspnet_Users u, dbo.aspnet_Profile p, #PageIndexForUsers i
WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= @PageLowerBound AND i.IndexId <= @PageUpperBound
SELECT COUNT(*)
FROM #PageIndexForUsers ---计算临时表的记录数
DROP TABLE #PageIndexForUsers --删除临时表
/*既然不用话为什么还要弄一个分页的出来,不知道干什么用来着,不过前面有这个参数那就肯定有用了*/
END