在 T-SQL 过程中建立上限/下限

Question

我正在尝试在我的存储过程中建立上限/下限 下面，最后遇到了一些问题（我没有得到任何结果 其中，如果没有临时表内部连接，我会得到预期的结果）。 我在尝试加入临时表中的列时需要一些帮助#PageIndexForUsers 我的连接声明的其余部分，我搞砸了一些东西 这个声明：

<前><代码>内连接 #PageIndexForUsers ON ( dbo.aspnet_Users.UserId = #PageIndexForUsers.UserId AND #PageIndexForUsers.IndexId >= @PageLowerBound AND #PageIndexForUsers.IndexId <= @PageUpperBound ）

此时我可以使用反馈 - 以及有关如何改进的任何建议 我的程序的逻辑（如果您看到其他需要改进的地方）也值得赞赏。

提前致谢...

ALTER PROCEDURE dbo.wb_Membership_GetAllUsers
    @ApplicationName       nvarchar(256),
    @sortOrderId           smallint = 0,
    @PageIndex             int,
    @PageSize              int
AS
BEGIN

    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName

    IF (@ApplicationId IS NULL)
        RETURN 0

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

BEGIN TRY

    -- 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_Membership m, dbo.aspnet_Users u
    WHERE  u.ApplicationId = @ApplicationId AND u.UserId = m.UserId
    ORDER BY u.UserName

    SELECT @TotalRecords = @@ROWCOUNT

    SELECT dbo.wb_Profiles.profileid, dbo.wb_ProfileData.firstname, dbo.wb_ProfileData.lastname, dbo.wb_Email.emailaddress, dbo.wb_Email.isconfirmed, dbo.wb_Email.emaildomain, dbo.wb_Address.streetname, dbo.wb_Address.cityorprovince, dbo.wb_Address.state, dbo.wb_Address.postalorzip, dbo.wb_Address.country, dbo.wb_ProfileAddress.addresstype,dbo.wb_ProfileData.birthday, dbo.wb_ProfileData.gender, dbo.wb_Session.sessionid, dbo.wb_Session.lastactivitydate, dbo.aspnet_Membership.userid, dbo.aspnet_Membership.password, dbo.aspnet_Membership.passwordquestion, dbo.aspnet_Membership.passwordanswer, dbo.aspnet_Membership.createdate
FROM dbo.wb_Profiles 
INNER JOIN dbo.wb_ProfileAddress
ON 
(
 dbo.wb_Profiles.profileid = dbo.wb_ProfileAddress.profileid
 AND dbo.wb_ProfileAddress.addresstype = 'home'
)
INNER JOIN dbo.wb_Address
ON dbo.wb_ProfileAddress.addressid = dbo.wb_Address.addressid
INNER JOIN dbo.wb_ProfileData 
ON dbo.wb_Profiles.profileid = dbo.wb_ProfileData.profileid 
INNER JOIN dbo.wb_Email 
ON 
(
 dbo.wb_Profiles.profileid = dbo.wb_Email.profileid 
 AND dbo.wb_Email.isprimary = 1
)
INNER JOIN dbo.wb_Session
ON dbo.wb_Profiles.profileid = dbo.wb_Session.profileid 
INNER JOIN
dbo.aspnet_Membership
ON dbo.wb_Profiles.userid = dbo.aspnet_Membership.userid
INNER JOIN
dbo.aspnet_Users
ON dbo.aspnet_Membership.UserId = dbo.aspnet_Users.UserId
INNER JOIN
dbo.aspnet_Applications 
ON dbo.aspnet_Users.ApplicationId = dbo.aspnet_Applications.ApplicationId
INNER JOIN 
#PageIndexForUsers ON  ( dbo.aspnet_Users.UserId =
#PageIndexForUsers.UserId     AND #PageIndexForUsers.IndexId >= @PageLowerBound AND
#PageIndexForUsers.IndexId <= @PageUpperBound )
ORDER BY CASE @sortOrderId
    WHEN 1 THEN dbo.wb_ProfileData.lastname
    WHEN 2 THEN dbo.wb_Profiles.username
    WHEN 3 THEN dbo.wb_Address.postalorzip
    WHEN 4 THEN dbo.wb_Address.state
END

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0 ROLLBACK TRAN    
    EXEC wb_ErrorHandler
    RETURN 55555

END CATCH

   RETURN @TotalRecords

END
GO

Answer 1

#PageIndexForUsers 中没有足够的行，不是吗？

如果 @PageSize = 50 并且您需要 @PageIndex 2，那么您将从 #PageIndexForUsers 中查找第 100 到 149 行。你有这么多行吗？

行过滤器应应用于从 FROM dbo.wb_Profiles 开始的较大数据集