CODEWITHSUNDEEP
sql-serverstored-procedures
Tushar
Tushar

Reputation: 410

Stored procedure for pagination is not working

I am implementing pagination in my asp.net application. For this, I have created a stored procedure to get records from the CommunityPost table. But this stored procedure is not working correctly. It does not return any records.

My stored procedure is:

ALTER PROCEDURE [dbo].[CommunityPostLoadAllPaged]
(
    @PageIndex    int = 0, 
    @PageSize     int = 2147483644,
    @TotalRecords int = null OUTPUT
)
AS
BEGIN
    DECLARE @sql nvarchar(max)

    --paging
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int 
    DECLARE @RowsToReturn int

    SET @RowsToReturn = @PageSize * (@PageIndex + 1)    
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize + 1

    CREATE TABLE #DisplayOrderTmp 
    (
        [Id] int IDENTITY (1, 1) NOT NULL,
        [CommunityPostId] int NOT NULL
    )

    SET @sql = '
    INSERT INTO #DisplayOrderTmp ([CommunityPostId])
    SELECT p.Id
    FROM
        CommunityPost p with (NOLOCK)'


    CREATE TABLE #PageIndex 
    (
        [IndexId] int IDENTITY (1, 1) NOT NULL,
        CommunityPostId int NOT NULL
    )

    INSERT INTO #PageIndex (CommunityPostId)
        SELECT CommunityPostId
        FROM #DisplayOrderTmp
        GROUP BY CommunityPostId
        ORDER BY min([Id])

    SELECT *
    FROM #PageIndex

    --total records
    SET @TotalRecords = @@rowcount

    select * from #DisplayOrderTmp

    DROP TABLE #DisplayOrderTmp

    select * from  #PageIndex

    --return products
    SELECT TOP (@RowsToReturn)
        p.*
    FROM
        #PageIndex [pi]
    INNER JOIN 
        dbo.CommunityPost p WITH (NOLOCK) ON p.Id = [pi].CommunityPostId
    WHERE
        [pi].IndexId > @PageLowerBound AND 
        [pi].IndexId < @PageUpperBound
    ORDER BY
        [pi].IndexId

    DROP TABLE #PageIndex
END

Table schema of CommunityPost table :

ColumnName       DataType
================================
Id               int
SharerId         int
Text             nvarchar(MAX)
Published        bit
CreatedOnUtc     datetime

How can I get CommunityPost records for pagination implementation?

Please help me.

Thank you in advance.

Upvotes: 0

Views: 220

Answers (2)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try to this

ALTER PROCEDURE [dbo].[CommunityPostLoadAllPaged]
(
    @PageIndex    int = 0, 
    @PageSize     int = 50,
    @TotalRecords int = null OUTPUT
)
AS
BEGIN

       Declare  @inEndRow       Int
        ,@inStartRow        Int 

    CREATE TABLE #DisplayOrderTmp 
    (
        [Id] int IDENTITY (1, 1) NOT NULL,
        [inRowNum] Int  Primary Key,
        [CommunityPostId] int NOT NULL
    )


    INSERT INTO #DisplayOrderTmp ([CommunityPostId])
    SELECT ROW_NUMBER() OVER(ORDER BY p.Id asc), p.Id
    FROM
        CommunityPost p with (NOLOCK)


    Select   @TotalRecords = Count(1) 
            ,@inEndRow = ((@PageIndex     + @PageSize     ) + 1)
            ,@inStartRow = @PageIndex    
    From    #DisplayOrderTmp As d With (Nolock)


   Select    *
    From    #DisplayOrderTmp  As d With (Nolock)
    Where   d.inRowNum > @inStartRow
            And d.inRowNum < @inEndRow
END

Upvotes: 2

Mike K
Mike K

Reputation: 486

There's a lot of odd things in that query.

Most importantly: you're setting @sql as if to run some dynamic SQL, then - not calling it. I can't see any reason to run that query as dynamic SQL anyway, there's no variables in it. Just run the code already.

Second, you're running multiple SELECTs within the procedure. That's a common debugging technique, but probably not what you want in the finished code. If you really want it to return a single table, then take out all the SELECTs before the final one (except those inserting into your temp tables, of course). As it is, it'll be outputting several sets of values, and I'm guessing that your calling code is only expecting one of them.

Third, what is ORDER BY Min(Id) meant to achieve? I guess you're aiming for ORDER BY id, but maybe not.

Fourth, your default value of @PageSize is huge, barely below the maximum limit for an INT. And you're multiplying it by something, and inserting that into another INT value. That's practically guaranteed to overflow, if the default ever gets used. Is that the intention?

Best of luck,

Upvotes: 0

Related Questions