Establishing Upper / Lower Bound in T-SQL Procedure

Question

Hi.

I am trying to establish upper / lower bound in my stored procedure below and am having some problems at the end (I am getting no results where, without the temp table inner join i get the expected results). I need some help where I am trying to join the columns in my temp table #PageIndexForUsers to the rest of my join statement and I am mucking something up with this statement:

INNER JOIN 
#PageIndexForUsers ON  ( dbo.aspnet_Users.UserId =
#PageIndexForUsers.UserId     AND #PageIndexForUsers.IndexId >= @PageLowerBound AND
#PageIndexForUsers.IndexId <= @PageUpperBound )

I could use feedback at this point - and, any advice on how to improve my procedure's logic (if you see anything else that needs improvement) is also appreciated.

Thanks in advance...

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

You don't have enough rows in #PageIndexForUsers, no?

If @PageSize = 50 and you want @PageIndex 2, then you are looking for rows 100 to 149 from #PageIndexForUsers. Do you have this many rows?

The row filter should be applied over the larger dataset that starts FROM dbo.wb_Profiles