DECLARE @PageIndex int=1;
DECLARE @PageSize int=100;
DECLARE @cnt int;
DECLARE @Temp
TABLE(sno int,RowNumber int,wholert int,Totalwholert float,C_name Varchar(50),LOW float);
INSERT INTO @Temp
SELECT DISTINCT P.sno AS sno
,ROW_NUMBER() Over (ORDER BY P.sno asc) as RowNumber
,P.wholert AS wholert
,P.qty*P.wholert AS Totalwholert
,P.name+' -'+P.code AS C_name
,L.Low_stock_Shop1 AS LOW
FROM tbl_Purchase_Stock AS P INNER JOIN tbl_Low_stk_Entry_details AS L
ON P.code=L.code
select @cnt=COUNT(1) FROM @Temp;
SELECT * FROM @Temp
WHERE
RowNumber BETWEEN (@PageIndex -1) * @PageSize + 1 AND
(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1;
select @cnt AS TotalRecords;