27,580
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------
---本代码运行环境MSSQL2005
---作者:zhzhrony
---代码是根据网上一些分页过程改的,你可以使用在任何地方;
----如果你对原代码进行修改,请保存以上这些信息
---------------------------------------------------
ALTER PROCEDURE dbo.gl_Public_FYPage --分页过程
@glTableName varchar(20), --表名
@glWhere varchar(100), --where条件
@glKey varchar(20), --主键
@glClunt varchar(500) ="*" ,--获得的列 *表示全部列
@glPageSize int ,--页显示的记录数
@glCrruPage int, --当前页
@glAscDesc varchar(60), --order by 条件
@glRecordCount int output --输出总记录数
AS
/* SET NOCOUNT ON */
declare @sql nvarchar(500); --SQL语句
--declare @PageConut int; --总页数
declare @glRecordCount1 int; --输出总记录数
---获得总记录数
if(@glWhere = '' or @glWhere = null)
set @sql='select @glRecordCount1=count(' + @glKey + ') from ' + @glTableName
else
set @sql='select @glRecordCount1=count(' + @glKey + ') from ' + @glTableName + ' where ' + @glWhere
EXEC sp_executesql @sql,N'@glRecordCount1 int OUTPUT',@glRecordCount OUTPUT--计算总记录数
--select @glRecordCount
--select @glRecordCount
--select @glRecordCount=@pc
---获得总页数
--set @PageConut=ceiling((@glRecordCount+0.0)/@glPageSize)
--获得第一页
if(@glCrruPage=1)
if (@glWhere = null or @glWhere = '')
set @sql='select top ' + cast(@glPageSize as varchar(4)) + ' ' + @glClunt + ' from ' + @glTableName + ' order by '+ @glAscDesc
else
set @sql='select top ' + cast(@glPageSize as varchar(4)) + ' ' + @glClunt + ' from ' + @glTableName + ' where ' + @glWhere + ' order by '+ @glAscDesc
else
if (@glWhere = null or @glWhere = '')
set @sql='select top ' + cast(@glPageSize as varchar(4)) + ' ' + @glClunt + ' from ' + @glTableName + ' where ' + @glKey +
' not in (select top ' + cast(@glPageSize * (@glCrruPage -1) as varchar(4)) + ' ' + @glKey + ' from ' + @glTableName + ' order by ' +
@glAscDesc + ') order by ' + @glAscDesc
else
set @sql='select top ' + cast(@glPageSize as varchar(4)) + ' ' + @glClunt + ' from ' + @glTableName + ' where (' + @glWhere + ') and ' + @glKey +
' not in (select top ' + cast(@glPageSize * (@glCrruPage -1) as varchar(4)) + ' ' + @glKey + ' from ' + @glTableName + ' where ' + @glWhere + ' order by ' +
@glAscDesc + ') order by ' + @glAscDesc
/*set @sql='select top ' + cast(@glPageSize as varchar(4)) + ' ' + @glClunt + ' from ' + @glTableName + ' where ' + @glWhere + @glKey +
' not in (select top 1 ' + @glKey + ' from ' + @glTableName + ' where ' + @glWhere + ' ' + @glKey + '=' + + cast(@glPageSize * (@glCrruPage -1) as varchar(4)) + ' order by ' +
@glKey + ' desc) order by ' + @glKey + ' desc'*/
Exec(@sql)
RETURN
ALTER PROCEDURE [dbo].[usp_aspnet_PageSelect]
@PageCount int output, --记录数
@PageSize int, --页大小
@PageIndex int, --页码
@TableName nvarchar(50), --视图 或 表
@FilterSql nvarchar(512), --筛选语句
@OrderSql nvarchar(50) --排序语句
AS
BEGIN
SET NOCOUNT OFF;
DECLARE @SQL nvarchar(4000)
--获得记录总数
SET @SQL='SELECT @PageCount=COUNT(*) FROM '+@TableName+' WHERE 1=1 '+@FilterSql
EXEC sp_executesql @SQL,N'@PageCount int output',@PageCount output
DECLARE @Top nvarchar(8)
DECLARE @COUNT nvarchar(8)
SET @Top=CONVERT(nvarchar(8),@PageSize)
SET @COUNT=CONVERT(nvarchar(8),(@PageSize*(@PageIndex-1)))
--获得分页结果
SET @SQL=
'SELECT TOP '+@Top+' * FROM '+
'(SELECT *,ROW_NUMBER() OVER ( '+@OrderSql+' ) AS PageSelect_RowNumber '+
'FROM '+@TableName+' WHERE 1=1 '+@FilterSql+' ) aspnet_PageSelect '+
'WHERE PageSelect_RowNumber>' +@COUNT
EXEC (@SQL)
END