CREATE PROCEDURE sp_GetPageData
@TableName sysname, --表名
@SQL varchar(500),--约束条件
@PageIndex int,--页所引,从0开始
@PageSize int,--分页大小
@orderDESC varchar(100),--倒排序字段,支持多个字段
@orderASC varchar(100),--顺排序字段,支持多个,字段先后顺序与倒排序一致
@RecordCount int out,--返回记录数
@PageCount int out--返回页数
AS
declare @num int-- 2005-12-21更改,直接用SQL返回记录数 by wjh
declare @vSQL nvarchar(2000)
set @vSQL = N'select @count=Count(*) from ' + @TableName + N' where ' + @SQL
exec sp_ExecuteSQL @vSQL, N'@count int output', @num output
select @RecordCount=@num
SET @PageCount = CEILING(@RecordCount * 1.0)/@PageSize
DECLARE @sqlStr varchar(1000)
IF @PageIndex = 0 OR @PageIndex < 1
SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' * FROM '+@TableName+' WHERE '+@SQL+' ORDER BY '+@orderDESC
ELSE IF @PageIndex = @PageCount -1
SET @sqlStr = 'SELECT * FROM (SELECT TOP ' +STR(@PageSize)
+' * FROM '+@TableName+' WHERE '+@SQL+' ORDER BY '+@orderASC+') TempTable ORDER BY '+@orderDESC
ELSE -- 此处可以加优化,从中间页往两边
SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' * FROM (SELECT TOP '
+ STR(@RecordCount - @PageSize * @PageIndex ) + ' * FROM '+@TableName
+' WHERE '+@SQL+' ORDER BY '+@orderASC+') TempTable ORDER BY '+@orderDESC
EXEC(@sqlStr)
GO
@TableName sysname, --表名
@SQL varchar(500),--约束条件
@PageIndex int,--页所引,从0开始
@PageSize int,--分页大小
@orderDESC varchar(100),--倒排序字段,支持多个字段
@orderASC varchar(100),--顺排序字段,支持多个,字段先后顺序与倒排序一致
@RecordCount int out,--返回记录数
@PageCount int out--返回页数
AS
declare @num int-- 2005-12-21更改,直接用SQL返回记录数 by wjh
declare @vSQL nvarchar(2000)
set @vSQL = N'select @count=Count(*) from ' + @TableName + N' where ' + @SQL
exec sp_ExecuteSQL @vSQL, N'@count int output', @num output
select @RecordCount=@num
SET @PageCount = CEILING(@RecordCount * 1.0)/@PageSize
DECLARE @sqlStr varchar(1000)
IF @PageIndex = 0 OR @PageIndex < 1
SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' * FROM '+@TableName+' WHERE '+@SQL+' ORDER BY '+@orderDESC
ELSE IF @PageIndex = @PageCount -1
SET @sqlStr = 'SELECT * FROM (SELECT TOP ' +STR(@PageSize)
+' * FROM '+@TableName+' WHERE '+@SQL+' ORDER BY '+@orderASC+') TempTable ORDER BY '+@orderDESC
ELSE -- 此处可以加优化,从中间页往两边
SET @sqlStr = 'SELECT TOP ' + STR(@PageSize) + ' * FROM (SELECT TOP '
+ STR(@RecordCount - @PageSize * @PageIndex ) + ' * FROM '+@TableName
+' WHERE '+@SQL+' ORDER BY '+@orderASC+') TempTable ORDER BY '+@orderDESC
EXEC(@sqlStr)
GO