SQL SERVER 2005分页存储过程
看了很多的分页存储过程，感觉用起来还是没有自己亲自写的用起来感觉亲切，虽然写的比较烂，但是对这个方面的提高还是只有靠自己亲自动手才知道其中的微妙之处.
存储过程代码如下所示：
存储过程
1 CREATE PROCEDURE [dbo].[TopPageList]
2 @strTable varchar(200), --表名 ("@strTable", "myUser");
3 @strColumn varchar(50), --按该列来进行分页 ("@strColumn", "UserId");
4 @strOrderColumn varchar(50), --排序字段order by XXX desc
5 @intOrder int,--排序的顺序 0 升序 1降序
6 @strColumnlist varchar(150) , --要查询出的字段列表,*表示全部字段 cmd.Parameters.Add("@strColumnlist", "*");
7 @strWhere varchar(800)='', --查询条件 cmd.Parameters.Add("@strWhere", "");
8 @intPageSize int, --每页记录数 cmd.Parameters.Add("@intPageSize", 15);
9 @intPageNum int, --指定页 cmd.Parameters.Add("@intPageNum", 5);
10 -- @intPageCount int OUTPUT , --总页数 SqlParameter paramPageCount = cmd.Parameters.Add("@intPageCount", SqlDbType.Int);
11 -- paramPageCount.Direction = ParameterDirection.Output;
12 @itemCount int OUTPUT
13 -- @doCount bit = 0, -- 返回, 非值则返回记录总数
14 AS
15 --设置相应的空格
16
17 --设置DESC ASC
18 if @intOrder=0 --0升序
19 set @strOrderColumn=' order by '+@strOrderColumn
20 else --降序
21 set @strOrderColumn=' order by '+@strOrderColumn +' desc '
22
23 DECLARE @sql nvarchar(2000) --用于构造SQL语句
24 DECLARE @where1 varchar(800) --构造条件语句
25 DECLARE @where2 varchar(800) --构造条件语句
26 IF @strWhere is null or rtrim(@strWhere)=''
27 -- 为了避免SQL关键字与字段、表名等连在一起，首先为传入的变量添加空格
28 BEGIN --没有查询条件
29 SET @where1=' WHERE '
30 SET @where2=' '
31 END
32 ELSE
33 BEGIN --有查询条件
34 SET @where1=' WHERE ('+@strWhere+') AND '
35 SET @where2=' WHERE ('+@strWhere+') '
36 END
37 ------构造SQL语句，计算总页数。计算公式为总页数= Ceiling ( 记录个数/ 页大小)
38 --计算总项数
39 SET @sql='SELECT @itemCount=COUNT('+@strColumn+') from '+@strTable +@where2
40 print(@sql)
41 EXEC sp_executesql @sql,N'@itemCount int OUTPUT',@itemCount OUTPUT
42 -- 1：直接计算 2：自己写个分页控件里面设置一下也可以~！
43 -- set @intPageCount =floor(cast(@itemCount as float)/@intPageSize)
44 -- if @intPageCount <cast(@itemCount as float)/@intPageSize
45 -- set @intPageCount =@intPageCount +1
46 --
47
48
49 --执行SQL语句，计算总页数，并将其放入@intPageCount变量中
50
51 --将总页数放到查询返回记录集的第一个字段前，此语句可省略
52 SET @strColumnlist=' '+ Cast(@itemCount as varchar(30)) + ' as itemCount,' +' '+ @strColumnlist
53 --+ Cast(@intPageCount as varchar(30)) + ' as PageCount,'
54 SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) + @strColumnlist +
55 ' FROM ' + @strTable + @where1 + ' '+
56 @strColumn + ' not in '+
57 ' (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) + ' ' +
58 @strColumn + ' FROM '+ @strTable+@where2+@strOrderColumn+') ' + @strOrderColumn
59 print(@sql)
60
61 --ELSE
62 -- begin --构造降序的SQL ---针对2个表的时候会出现聚合函数的异常--适合单个表格的数据库分页操作
63 -- SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) + @strColumnlist +
64 -- ' FROM ' + @strTable + @where1 + ' '+
65 -- @strColumn + '<(SELECT MIN('+@strColumn+') '+
66 -- ' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) + ' ' +
67 -- @strColumn + ' FROM '+ @strTable+@where2+@strOrderColumn+') as tblTmp)' + @strOrderColumn
68 -- print(@sql)
69 -- end
70 IF @intPageNum=1--第一页
71 SET @sql='SELECT TOP '+CAST(@intPageSize AS varchar) + @strColumnlist + ' FROM '+@strTable+
72 @where2+@strOrderColumn
73 --END
74 --PRINT @sql
75 print(@sql)
76 exec(@sql)
77
2 @strTable varchar(200), --表名 ("@strTable", "myUser");
3 @strColumn varchar(50), --按该列来进行分页 ("@strColumn", "UserId");
4 @strOrderColumn varchar(50), --排序字段order by XXX desc
5 @intOrder int,--排序的顺序 0 升序 1降序
6 @strColumnlist varchar(150) , --要查询出的字段列表,*表示全部字段 cmd.Parameters.Add("@strColumnlist", "*");
7 @strWhere varchar(800)='', --查询条件 cmd.Parameters.Add("@strWhere", "");
8 @intPageSize int, --每页记录数 cmd.Parameters.Add("@intPageSize", 15);
9 @intPageNum int, --指定页 cmd.Parameters.Add("@intPageNum", 5);
10 -- @intPageCount int OUTPUT , --总页数 SqlParameter paramPageCount = cmd.Parameters.Add("@intPageCount", SqlDbType.Int);
11 -- paramPageCount.Direction = ParameterDirection.Output;
12 @itemCount int OUTPUT
13 -- @doCount bit = 0, -- 返回, 非值则返回记录总数
14 AS
15 --设置相应的空格
16
17 --设置DESC ASC
18 if @intOrder=0 --0升序
19 set @strOrderColumn=' order by '+@strOrderColumn
20 else --降序
21 set @strOrderColumn=' order by '+@strOrderColumn +' desc '
22
23 DECLARE @sql nvarchar(2000) --用于构造SQL语句
24 DECLARE @where1 varchar(800) --构造条件语句
25 DECLARE @where2 varchar(800) --构造条件语句
26 IF @strWhere is null or rtrim(@strWhere)=''
27 -- 为了避免SQL关键字与字段、表名等连在一起，首先为传入的变量添加空格
28 BEGIN --没有查询条件
29 SET @where1=' WHERE '
30 SET @where2=' '
31 END
32 ELSE
33 BEGIN --有查询条件
34 SET @where1=' WHERE ('+@strWhere+') AND '
35 SET @where2=' WHERE ('+@strWhere+') '
36 END
37 ------构造SQL语句，计算总页数。计算公式为总页数= Ceiling ( 记录个数/ 页大小)
38 --计算总项数
39 SET @sql='SELECT @itemCount=COUNT('+@strColumn+') from '+@strTable +@where2
40 print(@sql)
41 EXEC sp_executesql @sql,N'@itemCount int OUTPUT',@itemCount OUTPUT
42 -- 1：直接计算 2：自己写个分页控件里面设置一下也可以~！
43 -- set @intPageCount =floor(cast(@itemCount as float)/@intPageSize)
44 -- if @intPageCount <cast(@itemCount as float)/@intPageSize
45 -- set @intPageCount =@intPageCount +1
46 --
47
48
49 --执行SQL语句，计算总页数，并将其放入@intPageCount变量中
50
51 --将总页数放到查询返回记录集的第一个字段前，此语句可省略
52 SET @strColumnlist=' '+ Cast(@itemCount as varchar(30)) + ' as itemCount,' +' '+ @strColumnlist
53 --+ Cast(@intPageCount as varchar(30)) + ' as PageCount,'
54 SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) + @strColumnlist +
55 ' FROM ' + @strTable + @where1 + ' '+
56 @strColumn + ' not in '+
57 ' (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) + ' ' +
58 @strColumn + ' FROM '+ @strTable+@where2+@strOrderColumn+') ' + @strOrderColumn
59 print(@sql)
60
61 --ELSE
62 -- begin --构造降序的SQL ---针对2个表的时候会出现聚合函数的异常--适合单个表格的数据库分页操作
63 -- SET @sql='SELECT TOP '+ CAST(@intPageSize AS varchar) + @strColumnlist +
64 -- ' FROM ' + @strTable + @where1 + ' '+
65 -- @strColumn + '<(SELECT MIN('+@strColumn+') '+
66 -- ' FROM (SELECT TOP '+ CAST(@intPageSize*(@intPageNum - 1) AS varchar) + ' ' +
67 -- @strColumn + ' FROM '+ @strTable+@where2+@strOrderColumn+') as tblTmp)' + @strOrderColumn
68 -- print(@sql)
69 -- end
70 IF @intPageNum=1--第一页
71 SET @sql='SELECT TOP '+CAST(@intPageSize AS varchar) + @strColumnlist + ' FROM '+@strTable+
72 @where2+@strOrderColumn
73 --END
74 --PRINT @sql
75 print(@sql)
76 exec(@sql)
77
相应的操作代码如下所示：
C#操作代码
1 public static void BindingContent(string strTable, string strColumn, string strOrderColumn, int intOrder, string strColumnlist, string strWhere, IChangePageStored changePage)
2 {
3
4 SqlParameter[] paras=new SqlParameter[9];
5 paras[0] = new SqlParameter("@strTable" ,SqlDbType.VarChar);
6 paras[0].Value = strTable;
7 paras[1] = new SqlParameter("@strColumn", SqlDbType.VarChar);
8 paras[1].Value = strColumn;
9 paras[2] = new SqlParameter("@strOrderColumn", SqlDbType.VarChar);
10 paras[2].Value = strOrderColumn;
11 paras[3] = new SqlParameter("@strColumnlist", SqlDbType.VarChar);
12 paras[3].Value = strColumnlist;
13 paras[4] = new SqlParameter("@intOrder", SqlDbType.Int);
14 paras[4].Value = intOrder;
15 paras[5] = new SqlParameter("@strWhere", SqlDbType.VarChar);
16 paras[5].Value = strWhere;
17 paras[6] = new SqlParameter("@intPageSize", SqlDbType.Int);
18 paras[6].Value = changePage.PageSize;
19 paras[7] = new SqlParameter("@intPageNum", SqlDbType.Int);
20 paras[7].Value = changePage.CurrentPage ;
21 // paras[8] = new SqlParameter("@intPageCount", SqlDbType.Int);
22 // paras[8].Direction = ParameterDirection.Output;
23 paras[8] = new SqlParameter("@itemCount", SqlDbType.Int);
24 paras[8].Direction = ParameterDirection.Output;
25 DataSet ds = DBTool.ExecuteDataset(CommandType.StoredProcedure, "TopPageList", paras);
26
27 /* @intPageCount int OUTPUT , --总页数 SqlParameter paramPageCount = cmd.Parameters.Add("@intPageCount", SqlDbType.Int);
28 -- paramPageCount.Direction = ParameterDirection.Output;
29
30 @strTable = N'zhq_in_content c INNER JOIN zhp_in_columns m ON c.columns_id=m.columns_id',
31 @strColumn = N'c.content_id',
32 @strOrderColumn = N'c.createdate',
33 @intOrder = 1,
34 @strColumnlist = N'*',
35 @strWhere = N'c.status=0 AND c.del=0',
36 @intPageSize = 20,
37 @intPageNum =100,*/
38 changePage.DataSource = ds.Tables[0].DefaultView;// 设置分页控件的数据
39 if (ds != null && ds.Tables[0].Rows.Count > 0)
40 {
41 changePage.RecordCount = int.Parse(ds.Tables[0].Rows[0]["itemCount"].ToString());
42 // changePage.PageCount = int.Parse(ds.Tables[0].Rows[0]["PageCount"].ToString());
43 }
44 if (changePage.DataUI.GetType().BaseType.Name == "BaseDataList")
45 {
46 changePage.DataUI.DataSource = changePage.DataSource;//设置数据源控件的数据
47 changePage.DataUI.DataBind();
48
49 }
50
51 }
52
2 {
3
4 SqlParameter[] paras=new SqlParameter[9];
5 paras[0] = new SqlParameter("@strTable" ,SqlDbType.VarChar);
6 paras[0].Value = strTable;
7 paras[1] = new SqlParameter("@strColumn", SqlDbType.VarChar);
8 paras[1].Value = strColumn;
9 paras[2] = new SqlParameter("@strOrderColumn", SqlDbType.VarChar);
10 paras[2].Value = strOrderColumn;
11 paras[3] = new SqlParameter("@strColumnlist", SqlDbType.VarChar);
12 paras[3].Value = strColumnlist;
13 paras[4] = new SqlParameter("@intOrder", SqlDbType.Int);
14 paras[4].Value = intOrder;
15 paras[5] = new SqlParameter("@strWhere", SqlDbType.VarChar);
16 paras[5].Value = strWhere;
17 paras[6] = new SqlParameter("@intPageSize", SqlDbType.Int);
18 paras[6].Value = changePage.PageSize;
19 paras[7] = new SqlParameter("@intPageNum", SqlDbType.Int);
20 paras[7].Value = changePage.CurrentPage ;
21 // paras[8] = new SqlParameter("@intPageCount", SqlDbType.Int);
22 // paras[8].Direction = ParameterDirection.Output;
23 paras[8] = new SqlParameter("@itemCount", SqlDbType.Int);
24 paras[8].Direction = ParameterDirection.Output;
25 DataSet ds = DBTool.ExecuteDataset(CommandType.StoredProcedure, "TopPageList", paras);
26
27 /* @intPageCount int OUTPUT , --总页数 SqlParameter paramPageCount = cmd.Parameters.Add("@intPageCount", SqlDbType.Int);
28 -- paramPageCount.Direction = ParameterDirection.Output;
29
30 @strTable = N'zhq_in_content c INNER JOIN zhp_in_columns m ON c.columns_id=m.columns_id',
31 @strColumn = N'c.content_id',
32 @strOrderColumn = N'c.createdate',
33 @intOrder = 1,
34 @strColumnlist = N'*',
35 @strWhere = N'c.status=0 AND c.del=0',
36 @intPageSize = 20,
37 @intPageNum =100,*/
38 changePage.DataSource = ds.Tables[0].DefaultView;// 设置分页控件的数据
39 if (ds != null && ds.Tables[0].Rows.Count > 0)
40 {
41 changePage.RecordCount = int.Parse(ds.Tables[0].Rows[0]["itemCount"].ToString());
42 // changePage.PageCount = int.Parse(ds.Tables[0].Rows[0]["PageCount"].ToString());
43 }
44 if (changePage.DataUI.GetType().BaseType.Name == "BaseDataList")
45 {
46 changePage.DataUI.DataSource = changePage.DataSource;//设置数据源控件的数据
47 changePage.DataUI.DataBind();
48
49 }
50
51 }
52
分类:
[10] SQL Server
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 提示词工程——AI应用必不可少的技术
· Open-Sora 2.0 重磅开源！
· 周边上新：园子的第一款马克杯温暖上架