Please refer below script.
SQL
CREATE PROCEDURE [GetImagesPageWiseBasedOnRange]
@PageIndex INT = 1
,@PageSize INT = 3
,@Start INT
,@End INT
,@PageCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [Id] ASC
)AS RowNumber
,Id
,Name
,Url
INTO #Results
FROM [Images]
WHERE (Id BETWEEN @Start AND @End) OR (@Start = 0 AND @End = 0)
DECLARE @RecordCount INT
SELECT @RecordCount = COUNT(*) FROM #Results
SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
I hope this will help you out.
