order by id desc
offset @pageNum row fetch next @pageSize rows only;
CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
/***************************************************************
参数说明:
1.Tables :表名称,视图，不带dbo.
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc (Desc Asc前必须加空格)
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Fields: 字段名
7.Filter :过滤语句，不带Where
8.Group :Group语句,不带Group By
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100) = NULL,
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 20,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
BEGIN
SET NOCOUNT ON
/*默认排序*/
SET @Sort = RTRIM(LTRIM(@Sort))
IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @SQL1 varchar(4000)
/*设定排序语句.*/
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50),@strEndRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strGroup varchar(1000)
/*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
SET @strEndRow = CAST((@CurrentPage*@PageSize) AS varchar(50))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' Where ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
BEGIN
SET @strGroup = ' GROUP BY ' + @Group + ' '
END
ELSE
BEGIN
SET @strGroup = ''
END
/*执行查询语句*/
SET @SQL1 ='select * from (Select ' + @Fields + ',ROW_NUMBER() OVER(Order By '+@Sort+') as row FROM ' + @Tables + @strFilter + @strGroup + ') a where row between '+@strStartRow+' and '+@strEndRow
--PRINT (@SQL1)
EXEC (@SQL1)
END