sql server2005 rownum通用存储过程分页
来源:互联网 发布:无名智者云签到源码 编辑:程序博客网 时间:2024/05/21 20:29
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_GetRecordFromPage]
@TableName varchar(350), --表名
@Fields varchar(5000) = "*", --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1, --指定当前为第几页
@distinct VARCHAR(50)=NULL, --去除重复值,注意只能是一个字段
@top INT=NULL --查询TOP,不传为全部
AS
BEGIN
Declare @sql nvarchar(4000);
Declare @totalRecord int;
DECLARE @totalPage INT;
--计算总记录数
IF (@distinct IS NULL OR @distinct="")
BEGIN
IF (@SqlWhere="" OR @sqlWhere IS NULL)
SET @sql = "select @totalRecord = count(1) from " + @TableName
ELSE
SET @sql = "select @totalRecord = count(1) from " + @TableName + " where " + @sqlWhere
END
ELSE
BEGIN
IF (@SqlWhere="" OR @sqlWhere IS NULL)
SET @sql = "select @totalRecord = count(distinct " + @distinct + ") from " + @TableName
ELSE
SET @sql = "select @totalRecord = count(distinct " + @distinct + ") from " + @TableName + " where " + @sqlWhere
END
EXEC sp_executesql @sql,N"@totalRecord int OUTPUT",@totalRecord OUTPUT--计算总记录数
IF(@top>0)
BEGIN
--指定TOP 记录
SET @Fields= "top " + CAST(@top AS VARCHAR(20)) + " " + @Fields;
--如果总记录数超过TOP数,设总记录数为TOP数
IF(@totalRecord>@top)
SET @totalRecord=@top
END
--计算总页数
SELECT @totalPage=CEILING((@totalRecord+0.0)/@PageSize)
SELECT @totalRecord AS "fldtotalRecord",@totalPage AS "fldTotalPage"
IF (@distinct IS NULL OR @distinct="")
BEGIN
IF (@SqlWhere="" or @sqlWhere IS NULL)
SET @sql = "Select * FROM (select " + @Fields + ",ROW_NUMBER() Over(order by " + @OrderField + ") as rowId from " + @TableName
ELSE
SET @sql = "Select * FROM (select " + @Fields + ",ROW_NUMBER() Over(order by " + @OrderField + ") as rowId from " + @TableName + " where " + @SqlWhere
END
ELSE
BEGIN
IF (@SqlWhere="" or @sqlWhere IS NULL)
SET @sql = "Select * FROM (select " + @Fields + ",ROW_NUMBER() Over(order by " + @OrderField + ") as rowId from " + @TableName
ELSE
SET @sql = "Select * FROM (select " + @Fields + ",ROW_NUMBER() Over(order by " + @OrderField + ") as rowId from " + @TableName + " where " + @SqlWhere
SET @sql=@sql + " GROUP BY " + @distinct;
END
--处理页数超出范围情况
IF @PageIndex<=0
SET @pageIndex = 1
--处理开始点和结束点
DECLARE @StartRecord INT
DECLARE @EndRecord int
SET @StartRecord = (@pageIndex-1)*@PageSize + 1
SET @EndRecord = @StartRecord + @pageSize - 1
--继续合成sql语句
SET @sql = @sql + ") as tempTable where rowId >=" + CONVERT(VARCHAR(50),@StartRecord) + " and rowid<= " + CONVERT(VARCHAR(50),@EndRecord)
Exec(@sql)
end
- sql server2005 rownum通用存储过程分页
- sql server2005 rownum通用存储过程分页
- sql server2005 rownum通用存储过程分
- SQL Server2005通用分页存储过程
- SQL Server2005通用分页存储过程
- SQL Server2005通用分页存储过程
- 一个sql server2005分页的存储过程
- SQL 通用分页存储过程
- SQL 通用分页存储过程
- sql通用分页存储过程
- sql 通用分页存储过程
- sql 通用分页存储过程
- SQL分页通用存储过程
- 通用sql分页存储过程
- 通用SQL分页存储过程
- 【转载】Sql Server2005不同分页存储过程的性能对比
- Sql Server2005用Row_Number分页存储过程的性能实测
- Sql Server2005学习笔记(存储过程:分页小程序)
- 大学最后一个暑假
- C++的explicit关键字
- Struts之form中reset方法的用途——(解决)
- 关于sql 2005安装 警告CMD+的解决方法.
- Sql Server 2005 数据库备份还原后出现“受限制用户”问题的解决
- sql server2005 rownum通用存储过程分页
- Oracle存储过程分页
- 实习日志(二)
- java.util.Collections学习
- 文件系统注册
- VC窗口刷新InvalidateRect和UpdateWindow
- 解决:终端服务器超出了最大允许连接数
- 修复鼠标单击变双击的问题
- 《QTP自动化测试进阶》一书附带的源代码