Before a while ago, I have posted query for the true paging but it does not work in SQL server as ROWNUM is not available in it.
Here, is the sample stored procedure for SQL Server 2000
CREATE PROCEDURE [dbo].[State_Search]
@SearchText nvarchar(50) = null
,@PageNo int
,@PageSize int
,@TotalRecords int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StartFrom int
SET @StartFrom = @PageSize * (@PageNo - 1);
CREATE TABLE #tmpState (
RowNo int IDENTITY(1,1) PRIMARY KEY,
RowId int NOT NULL
);
INSERT INTO #tmpState(RowId)
SELECT CHECKSUM([State].StateGuid) FROM [State]
WHERE (@SearchText IS NULL OR StateCode LIKE @SearchText OR
StateName LIKE @SearchText OR StateDesc LIKE @SearchText)
SET @TotalRecords = (SELECT COUNT(*) FROM #tmpState);
SELECT
[State].*
FROM #tmpState
INNER JOIN [State] ON CHECKSUM([State].StateGuid) = #tmpState.RowId
WHERE #tmpState.RowNo > @StartFrom AND #tmpState.RowNo <= (@StartFrom + @PageSize)
ORDER BY #tmpState.RowNo;
SELECT @TotalRecords AS TotalRecords;
DROP TABLE #tmpState;
END
