sqlserver中千万数量级分页存储过程代码
2023-06-13 09:13:55 时间
/* *************************************************************** ** 中国无忧商务网千万数量级分页存储过程 ** *************************************************************** 参数说明: 1.Tables :表名称,视图 2.PrimaryKey :主关键字 3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc 4.CurrentPage :当前页码 5.PageSize :分页尺寸 6.Filter :过滤语句,不带Where 7.Group :Group语句,不带Group By 版权所有:中国无忧商务网(http://www.cn5135.com) 欢迎试用,有什么心得请 M
CREATE PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 中国无忧商务网千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
版权所有:中国无忧商务网(http://www.cn5135.com)
欢迎试用,有什么心得请 Mail:xzhijun@GMail.com
注:转载请保留相关版权说明,谢谢!^_^
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = "*",
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ""
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX("DESC",@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, "DESC", "")
SET @operator = "<="
END
ELSE
BEGIN
IF CHARINDEX("ASC", @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, "ASC", "")
SET @operator = ">="
END
IF CHARINDEX(".", @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX(".",@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX(".",@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX("char", @type) > 0
SET @type = @type + "(" + CAST(@prec AS varchar) + ")"
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter 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))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ""
BEGIN
SET @strFilter = " WHERE " + @Filter + " "
SET @strSimpleFilter = " AND " + @Filter + " "
END
ELSE
BEGIN
SET @strSimpleFilter = ""
SET @strFilter = ""
END
IF @Group IS NOT NULL AND @Group != ""
SET @strGroup = " GROUP BY " + @Group + " "
ELSE
SET @strGroup = ""
/*执行查询语句*/
EXEC(
"
DECLARE @SortColumn " + @type + "
SET ROWCOUNT " + @strStartRow + "
SELECT @SortColumn=" + @strSortColumn + " FROM " + @Tables + @strFilter + " " + @strGroup + " ORDER BY " + @Sort + "
SET ROWCOUNT " + @strPageSize + "
SELECT " + @Fields + " FROM " + @Tables + " WHERE " + @strSortColumn + @operator + " @SortColumn " + @strSimpleFilter + " " + @strGroup + " ORDER BY " + @Sort + "
"
)
GO
CREATE PROCEDURE CN5135_SP_Pagination
/*
***************************************************************
** 中国无忧商务网千万数量级分页存储过程 **
***************************************************************
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句,不带Where
7.Group :Group语句,不带Group By
版权所有:中国无忧商务网(http://www.cn5135.com)
欢迎试用,有什么心得请 Mail:xzhijun@GMail.com
注:转载请保留相关版权说明,谢谢!^_^
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = "*",
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS
/*默认排序*/
IF @Sort IS NULL OR @Sort = ""
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
/*设定排序语句.*/
IF CHARINDEX("DESC",@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, "DESC", "")
SET @operator = "<="
END
ELSE
BEGIN
IF CHARINDEX("ASC", @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, "ASC", "")
SET @operator = ">="
END
IF CHARINDEX(".", @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX(".",@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX(".",@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX("char", @type) > 0
SET @type = @type + "(" + CAST(@prec AS varchar) + ")"
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter 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))
/*筛选以及分组语句.*/
IF @Filter IS NOT NULL AND @Filter != ""
BEGIN
SET @strFilter = " WHERE " + @Filter + " "
SET @strSimpleFilter = " AND " + @Filter + " "
END
ELSE
BEGIN
SET @strSimpleFilter = ""
SET @strFilter = ""
END
IF @Group IS NOT NULL AND @Group != ""
SET @strGroup = " GROUP BY " + @Group + " "
ELSE
SET @strGroup = ""
/*执行查询语句*/
EXEC(
"
DECLARE @SortColumn " + @type + "
SET ROWCOUNT " + @strStartRow + "
SELECT @SortColumn=" + @strSortColumn + " FROM " + @Tables + @strFilter + " " + @strGroup + " ORDER BY " + @Sort + "
SET ROWCOUNT " + @strPageSize + "
SELECT " + @Fields + " FROM " + @Tables + " WHERE " + @strSortColumn + @operator + " @SortColumn " + @strSimpleFilter + " " + @strGroup + " ORDER BY " + @Sort + "
"
)
GO
相关文章
- SQLServer存储过程创建和修改的实现代码
- 内存使用SQL Server共享内存实现高效数据传输(sqlserver共享)
- 快速使用JDBC连接SQL Server数据库(jdbc连接sqlserver数据库)
- 破解SQL Server:解决数据库管理中的挑战(破译sqlserver)
- 暂停SQL Server:快速解决系统故障!(暂停sqlserver)
- 找出成功之路:SqlServer 指引(找到sqlserver)
- 分布式SqlServer:改变数据存储方式的新动力(分布式sqlserver)
- SQLServer:页压缩带来的空间节约之旅(sqlserver页压缩)
- 降低版本:SQLServer的正确操作指南(sqlserver降版本)
- 存储过程如何快速使用SqlServer调用存储过程(sqlserver 调用)
- 如何使用SQLServer程序集提升效率(sqlserver程序集)
- 【SQL Server 之甲骨文:登上数据库最高峰】(sqlserver甲骨文)
- 处理基于SQLServer的实时流式数据处理技术(sqlserver 流式)
- 据使用SQLServer实现正确数据存储(sqlserver 正数)
- SQLServer:让数据安全从有道上的改变(sqlserver有道)
- SQLServer数据库扩展功能探索(sqlserver扩展库)
- SQL Server打开表:技巧指南(sqlserver打开表)
- 借助SQLServer快速搭建数据库(sqlserver 建库)
- SQLServer的存储之旅——照片存储技巧大全(sqlserver存照片)
- 查SQLserver数据库增删改查操作指南(sqlserver增删改)
- SQL Server 中如何优化垃圾表(sqlserver垃圾表)
- SQL Server操纵毫秒的技巧(sqlserver取毫秒)
- 存储实现高性能:使用SqlServer双网卡存储(sqlServer双网卡)
- SQLServer数据卸载攻略(sqlserver卸数)
- 精准管理:使用SQLServer分区优化数据库存储(sqlserver分区间)
- 利用SQLServer写出代码的技巧(sqlserver写代码)
- 如何快速关闭SQLServer服务(sqlserver关掉)
- 使用SQLServer修改日期的正确方法(sqlserver修改日期)
- 优雅编写SQLServer代码块的窍门(sqlserver代码块)
- SQLServer中的子类概述(sqlserver中子类)
- 构建未来——学习SQLServer虚拟表(sqlserver虚拟表)
- 长沙优化SQLServer:迈向极致性能(长沙sqlserver)
- C#操作图片读取和存储SQLserver实现代码