USE [BLTLNew]
GO
/****** Object: StoredProcedure [dbo].[usp_DataPager] Script Date: 2019-12-05 14:51:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DataPager]
(
@viewName VARCHAR(2000), --Table or View Name
@fieldName VARCHAR(800) = '*', --Query column
@pageSize INT = 20, --Pagesize
@pageNo INT =1, --current page
@orderString VARCHAR(2000), --order by
@whereString VARCHAR(MAX) = '1=1', --WHERE
@recordTotal INT OUTPUT --total count
)AS
BEGIN
DECLARE @beginRow INT
DECLARE @endRow INT
DECLARE @tempLimit VARCHAR(MAX)
DECLARE @tempCount NVARCHAR(MAX)
DECLARE @tempMain VARCHAR(MAX)
SET @beginRow = (@pageNo - 1) * @pageSize+ 1
SET @endRow = @pageNo * @pageSize
SET @tempLimit = 'rows BETWEEN ' + CAST(@beginRow AS VARCHAR) +' AND '+CAST(@endRow AS VARCHAR)
--return total count
SET @tempCount = 'SELECT @recordTotal = COUNT(*) FROM (SELECT 1 as a FROM '+@viewName+' WHERE '+@whereString+') As my_temp'
EXECUTE sp_executesql @tempCount,N'@recordTotal INT OUTPUT',@recordTotal OUTPUT
--return query data
SET @tempMain = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@orderString+') AS rows ,'+@fieldName+' FROM '+@viewName+' WHERE '+@whereString+') AS main_temp WHERE '+@tempLimit
EXECUTE (@tempMain)
END
