Using Order By in custom paging results in SQL Server

lingers
 
on Jul 12, 2021 11:23 PM
610 Views

I have this GridView that the order by id desc does not work online. It works perfectly well on my local machine, but the page i loaded online, the order by id desc does not work at all.

Please help

USE [kaging]
GO
/****** Object:  StoredProcedure [dbo].[boj]    Script Date: 7/12/2021 10:26:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[boj]
      @PageIndex INT = 1
      ,@PageSize INT = 15
      ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ROW_NUMBER() OVER (ORDER BY [id] DESC) AS RowNumber,*
    INTO #Results
    FROM [job]
  
    SELECT @RecordCount = COUNT(*)
    FROM #Results
             
    SELECT * FROM #Results
    WHERE RowNumber between (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1
       
    DROP TABLE #Results
END

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
lingers
 
on Jul 15, 2021 05:23 AM

this solved it 

ORDER BY id DESC not ORDER BY RowNumber DESC 

USE [kaging]
GO
/****** Object: StoredProcedure [dbo].[boj] Script Date: 7/15/2021 12:30:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[boj]
@PageIndex INT = 1
,@PageSize INT = 15
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER (ORDER BY [id] DESC) AS RowNumber,*
INTO #Results
FROM [job]

SELECT @RecordCount = COUNT(*)
FROM #Results

SELECT @RecordCount = COUNT(*)
FROM #Results

SELECT * FROM #Results
WHERE RowNumber between (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1
ORDER BY id DESC

DROP TABLE #Results
END