SQL Server public paging stored procedures and how to use them

--How to use: Execute in a stored procedure that gets a list of queries, and specify parameters on the line
--exec listpage @SQL, @PageSize, @PageNo, @OrderStr, @OrderType
--@SQL query statement
--Number of @PageSize data
--@PageNo Current page
--@OrderStr sort field
--@OrderType sort 0 ASC 1 DESC
Use [DataBaseName]--Database name
GO
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Listpage
@InSQL varchar (max),
@RowNum int,
@PageNo int,
@IndexWord varchar (200),
@Order bit=0
As
DECLARE @SQL varchar (max)
DECLARE @OrderMode varchar (10)
DECLARE @BeginRow int
DECLARE @EndRow int
BEGIN
IF @Order = 0
BEGIN
SET @OrderMode = 'ASC‘
END
ELSE
BEGIN
SET @OrderMode = 'DESC‘
END

IF @PageNo = 1
BEGIN
SET @BeginRow = 1
SET @EndRow = @RowNum
END
ELSE
BEGIN
SET @BeginRow = (@RowNum * (@PageNo-1)) +1
SET @EndRow = @BeginRow + @RowNum-1
END
SET @sql =' SELECT *,row_number () over (ORDER by' + @IndexWord + ' + @OrderMode + ') as ROWNUMBER from (' + @InSQL + ') as A '
SET @sql =' select * FROM (' + @sql + ') as B where b.rownumber between' + CONVERT (varchar), @BeginRow) + ' and' + CONVERT (varchar), @EndRow) + '
Exec (@sql)
END
GO

SQL Server public paging stored procedures and how to use them