True paging using SQL script

The system gets overloaded when we fetch complete data from the database and display a fixed sized of
data per page.

Although the data is distributed among different pages however it is fetched at a longer period of time

An efficient way is to fetch data per page.

Following example helps you search news and display results accordingly by stored procedure.

CREATE PROCEDURE [dbo].[News_Search]
@SearchText nvarchar(100) --Search text,what we want ot search in news.
,@PageNo int ----Page number
,@PageSize int-----Page size
,@TotalRecords int output--Total how many records are there in search result
AS
BEGIN

SET NOCOUNT ON;
DECLARE @StartFrom int
SET @StartFrom = @PageSize * (@PageNo - 1);
SET @TotalRecords=(SELECT ISNULL(COUNT(*),0)FROM ViwNews
WHERE ViwNews.NewsIsDisable <> 0 and
@SearchText Is Null OR ViwNews.NewsTitle Like @SearchText);

SELECT searchtable.*
FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY ViwNews.NewsTitle)AS [RecNo]
,ViwNews.*
FROM ViwNews
WHERE ViwNews.NewsIsDisable <> 0 and
@SearchText Is Null OR ViwNews.NewsTitle Like @SearchText
) AS searchtable
WHERE searchtable.RecNo > @StartFrom And searchtable.RecNo <=(@StartFrom+@PageSize);

END

In above query we have 4 parameters.

1) @SearchText used to pass for the search criteria.
2) @PageNo used to fetch which page number we want to fetch from the result.
3) @PageSize used to determine page size
4) @TotalRecords used to create number of pages
We can determine number of pages by dividing total records with page size.

In query we have used ROW_NUMBER() function which gives Record number obtained in the result sequentially.