ALTER PROCEDURE GetOnePage
@TableName VARCHAR(2000) , --表名
@ListFields VARCHAR(2000) , --字段名
@Where VARCHAR(2000), --条件语句(不用加where)
@OrderField VARCHAR(255) , --排序字段(必须!支持多字段)
@GetCount BIT = 0, --0返回记录总数 非0值则返回页记录集,默认全部
@PageSize INT , --每页多少条记录
@PageIndex INT = 1 --指定当前为第几页
AS
BEGIN
DECLARE @sql NVARCHAR(4000) ;
--计算总记录数
IF @GetCount = 0
BEGIN
IF ( @Where = ''
OR @Where = NULL
)
SET @sql = 'select count(*) as total from '
+ @TableName
ELSE
SET @sql = 'select count(*) as total from '
+ @TableName + ' where ' + @Where
-- select @sql
EXEC(@sql)
END
ELSE
BEGIN
IF ( @Where = ''
OR @Where = NULL
)
SET @sql = 'Select ' + @ListFields + ' FROM (select ROW_NUMBER() Over(order by '
+ @OrderField + ') as rowId,' + @ListFields + ' from '
+ @TableName
ELSE
SET @sql = 'Select ' + @ListFields + ' FROM (select ROW_NUMBER() Over(order by '
+ @OrderField + ') as rowId,' + @ListFields + ' from '
+ @TableName + ' where ' + @Where
--处理开始点和结束点
DECLARE @StartRecord INT
DECLARE @EndRecord INT
SET @StartRecord = ( @PageIndex - 1 ) * @PageSize + 1
SET @EndRecord = @StartRecord + @PageSize - 1
--继续合成sql语句
SET @Sql = @Sql + ') as t where rowId between '
+ CONVERT(VARCHAR(50), @StartRecord) + ' and '
+ CONVERT(VARCHAR(50), @EndRecord)
EXEC(@Sql)
END
END
@TableName VARCHAR(2000) , --表名
@ListFields VARCHAR(2000) , --字段名
@Where VARCHAR(2000), --条件语句(不用加where)
@OrderField VARCHAR(255) , --排序字段(必须!支持多字段)
@GetCount BIT = 0, --0返回记录总数 非0值则返回页记录集,默认全部
@PageSize INT , --每页多少条记录
@PageIndex INT = 1 --指定当前为第几页
AS
BEGIN
DECLARE @sql NVARCHAR(4000) ;
--计算总记录数
IF @GetCount = 0
BEGIN
IF ( @Where = ''
OR @Where = NULL
)
SET @sql = 'select count(*) as total from '
+ @TableName
ELSE
SET @sql = 'select count(*) as total from '
+ @TableName + ' where ' + @Where
-- select @sql
EXEC(@sql)
END
ELSE
BEGIN
IF ( @Where = ''
OR @Where = NULL
)
SET @sql = 'Select ' + @ListFields + ' FROM (select ROW_NUMBER() Over(order by '
+ @OrderField + ') as rowId,' + @ListFields + ' from '
+ @TableName
ELSE
SET @sql = 'Select ' + @ListFields + ' FROM (select ROW_NUMBER() Over(order by '
+ @OrderField + ') as rowId,' + @ListFields + ' from '
+ @TableName + ' where ' + @Where
--处理开始点和结束点
DECLARE @StartRecord INT
DECLARE @EndRecord INT
SET @StartRecord = ( @PageIndex - 1 ) * @PageSize + 1
SET @EndRecord = @StartRecord + @PageSize - 1
--继续合成sql语句
SET @Sql = @Sql + ') as t where rowId between '
+ CONVERT(VARCHAR(50), @StartRecord) + ' and '
+ CONVERT(VARCHAR(50), @EndRecord)
EXEC(@Sql)
END
END