sql2005分页存储过程
1. 如何利用 vc ado 连接 sql Server2005 实现分页存储过程
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Proc_Util_Page]') AND OBJECTPROPERTY(id, N'IsProcere') = 1)
DROP PROCEDURE dbo.Proc_Util_Page
GO
/*
分页
调用方法:EXEC Proc_Util_Page 'country,city', 'table', ' where 1=1', ' order by mid asc', 'mid', 1, 1, 0, ''
输入:
1.字段(不可为空)
2.表名(不可为空)
3.条件(可以为空,需要where)
4.排序(可以为空,需要order by,需要asc和desc字符)
5.主键(可以为空)
6.当前页数
7.每页记录数
8.输出总记录条数(若<1则执行count)
9.输出sql语句
返回:记录集
*/
CREATE PROCEDURE dbo.Proc_Util_Page
(
@sField nvarchar(1000),
@sTable nvarchar(1000),
@sWhere nvarchar(1000),
@sOrderby nvarchar(1000),
@sPkey nvarchar(50),
@iPageIndex int,
@iPageSize int,
@iRecordCount int OUTPUT,
@sOutsql nvarchar(4000) OUTPUT
)
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
DECLARE @iRC int, @sSQL nvarchar(4000), @sW nvarchar(1000), @sOB nvarchar(1000), @sT nvarchar(100)
SELECT @iRC = @iRecordCount, @sSQL = '', @sW = ' WHERE 1=1 ', @sOB = ''
--判断条件
IF RTRIM(@sWhere) != '' AND @sWhere IS NOT NULL
BEGIN
SET @sW=' ' + @sWhere + ' '
END
--判断总记录数
IF @iRC<1
BEGIN
SET @sSQL='SELECT @iRC=Count(*) FROM ' + @sTable + @sW
EXEC sp_executesql @sSQL,N'@iRC int OUT',@iRC OUT
END
--判断页数是否超出范围
SELECT @iPageIndex=(CASE WHEN @iRC<(@iPageIndex-1)*@iPageSize THEN CEILING(@iRC/@iPageSize) WHEN @iPageIndex<1 THEN 1 ELSE @iPageIndex END)
--判断排序
IF RTRIM(@sOrderby) != '' AND @sOrderby IS NOT NULL
BEGIN
SELECT @sOB=' ' + @sOrderby + ' '
END
--如果是第一页
IF @iPageIndex=1
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+@sOB
GOTO step4
END
--看有否主键
IF RTRIM(@sPkey) = '' OR @sPkey IS NULL
GOTO step1
ELSE
--看是否按主键排序
BEGIN
DECLARE @sOB1 nvarchar(1000), @sPkey1 nvarchar(50)
SELECT @sOB1 = UPPER(@sOrderby), @sPkey1 = UPPER(@sPkey)
IF CHARINDEX(@sPkey1 + ' ASC', @sOB1)>0
BEGIN
SET @sT='>(SELECT MAX('
GOTO step2
END
IF CHARINDEX(@sPkey1 + ' DESC', @sOB1)>0
BEGIN
SET @sT='<(SELECT MIN('
GOTO step2
END
GOTO step3
END
--如果无主键
step1:
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW + ' AND EXISTS (SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+@sOB+')'+@sOB+')'
GOTO step4
END
--纯按主键排序
step2:
BEGIN
SET @sSQL='SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sField+' FROM '+@sTable+@sW+' AND '+@sPkey+@sT+@sPkey+') FROM (SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW+@sOB+') AS tbTemp)'+@sOB
GOTO step4
END
--不纯按主键排序
step3:
BEGIN
SET @sSQL='SELECT '+@sField+' FROM '+@sTable+@sW + ' AND ' + @sPkey+' IN (SELECT TOP '+CAST(@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW + ' AND ' + @sPkey+' NOT IN(SELECT TOP '+CAST((@iPageIndex-1)*@iPageSize AS nvarchar)+' '+@sPkey+' FROM '+@sTable+@sW+@sOB+')'+@sOB+')'+@sOB
GOTO step4
END
--输出最终执行的分页sql语句并执行
step4:
SELECT @sOutsql = @sSQL, @iRecordCount = @iRC
--print(@sSQL)
EXEC(@sSQL)
SET NOCOUNT OFF
END
GO
---调用分页存储过程
declare @iRecordCount int,
@sOutsql nvarchar(4000)
EXEC Proc_Util_Page '字段,字段,字段,字段,字段', '表名', ' where 1=1', ' order by id asc', 'id', 1, 10, @iRecordCount output, @sOutsql output
2. 求Sql Server2005分页存储过程
/*=======================================2008.3.19 笨笨熊 18071777=============================================================*/
Create procere [dbo].[Pager]
(
@Psql nvarchar(4000), --生成dataset的语句
@PNum int, --显示第几页
@PSize int, --显示多少条
@Sort nvarchar(200) = null, --排序语句 如:order by id desc
@RowNumName nvarchar(50), --ROW_NUMBER别名
@Prcount int out, --返回记录总数
@Pcount int out --返回分页总数
)
as
set nocount on
declare @sqlTmp nvarchar(1000) --存放SQL语句
declare @sqlTmpCount nvarchar(1000) --存放查询记录总数量SQL语句
--计算范围
declare @Pmax int
declare @Pmin int
set @Pmax = @pnum*@psize --当前页*页大小 = 页最大值
set @Pmin = @Pmax - @psize +1 --页最大值 - 页大小 + 1 = 页最小值
set @sqlTmp='select * from ('+@Psql+')as temptb where '+@rowNumName+' BETWEEN '+cast(@Pmin as varchar(4))+' and '+cast(@Pmax as varchar(4))+''+@sort
set @sqlTmpCount='select @Prcount=count(*) from ('+@Psql+') as temptb'
----取得查询记录总数量-----
exec sp_executesql @sqlTmpCount,N'@Prcount int out ',@Prcount out
--取得分页总数
set @Pcount=(@Prcount+@pSize-1)/@pSize
exec sp_executesql @sqlTmp
set nocount off
/*调用测试
declare @Prcount int
declare @Pcount int
exec Pager 'select row_number() over(order by picid) AS rownum,* FROM userpic',1,3,'','rownum',1,1
print @Pcount*/
业务层调用：
/// <summary>
/// 根据用户自定义商品排序,分页取得用户收藏的商品
/// </summary>
/// <param name="pageIndex">当前页索引</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="recordCount">输出参数:总记录数</param>
/// <param name="pageCount">输出参数:总页数</param>
/// <returns>数据集(DataSet)</returns>
public DataSet GetShopsByPage(int pageIndex, int pageSize, out int recordCount, out int pageCount)
{
sql = "select row_number() over(order by picid) AS rownum,* FROM userpic";
return objDAO.GetDataByPage(sql, pageIndex, pageSize, "", "rownum", out recordCount, out pageCount);
}
数据访问层方法：
/// <summary>
/// 函数: 执行分页存储过程 , 返回数据集,适用于Sqlserver2005数据库
/// </summary>
/// <param name="psql">生成dataset的语句</param>
/// <param name="pNum">显示第几页</param>
/// <param name="pSize">显示多少条</param>
/// <param name="sort">排序语句 如:order by id desc </param>
/// <param name="rowNumName">ROW_NUMBER别名</param>
/// <param name="rCount">记录总数</param>
/// <param name="pCount">页总数</param>
/// <returns>返回数据集</returns>
public override DataSet GetDataByPage(string psql, int pNum, int pSize, string sort, string rowNumName, out int rCount, out int pCount)
{
OpenConn();
sqlCmd = new SqlCommand("Pager", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcere;
sqlCmd.Parameters.Add("@Psql", SqlDbType.NVarChar).Value = psql;
sqlCmd.Parameters.Add("@pNum", SqlDbType.Int).Value = pNum;
sqlCmd.Parameters.Add("@Psize", SqlDbType.Int).Value = pSize;
sqlCmd.Parameters.Add("@sort", SqlDbType.NVarChar).Value = sort;
sqlCmd.Parameters.Add("@rowNumName", SqlDbType.NVarChar).Value = rowNumName;
sqlCmd.Parameters.Add("@Prcount", SqlDbType.Int).Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add("@Pcount", SqlDbType.Int).Direction = ParameterDirection.Output;
//数据集对象
ds = new DataSet();
sqlDa = new SqlDataAdapter();
sqlDa.SelectCommand = sqlCmd;
//数据填充
sqlDa.Fill(ds);
//从输出参数中读取[总记录数]
rCount = (int)sqlCmd.Parameters["@Prcount"].Value;
//从输出参数中读取[总页数]
pCount = (int)sqlCmd.Parameters["@Pcount"].Value;
CloseConn();
return ds;
}
3. SQL Server 2005存储过程分页排序设置列名为变量
用字符串的方式来做,先生成查询字符串,然后执行
/*******************************************************************************
Author : Richard.Cai
CreateDate : 2008-12-22
UpdateDate : 2008-12-25
Amendment History:
Update by $$$$$$$$$$$ 2008/12/25 : Alter content.
*******************************************************************************/
ALTER PROCEDURE [dbo].[USP_AT_MR_SearchClient]
(
@PageSize INT = 10 --The size per page
,@PageStart INT = 0 --The page start,not contain this no..
,@WhereStr NVARCHAR(MAX) = '' --For example:' AND ClientName like '%AS%''
,@DebugFlag BIT = 0
)
AS
DECLARE @TableName NVARCHAR(255) --Only a table name
DECLARE @FieldName NVARCHAR(255) --Field name that data is unique.
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @sTMP NVARCHAR(MAX)
DECLARE @sOrderBy NVARCHAR(255)
/*Generate Paging Data*/
SELECT @sSQL=N'SELECT '+CHAR(13)
+N' ROW_NUMBER() OVER(ORDER BY SysID) AS PageID '+CHAR(13)
+N' ,Client_id As ClientCode '+CHAR(13)
+N' ,CAST(RTRIM(Client_e) AS NVARCHAR(50)) As ClientName '+CHAR(13)
+N' ,CAST(RTRIM(Client_c) AS NVARCHAR(50)) As ClientNameLocal '+CHAR(13)
+N'INTO #Client '+CHAR(13)
+N'FROM Client '+CHAR(13)
+N'WHERE 1 = 1 ' + @WhereStr +CHAR(13)
+N' '+CHAR(13)
+N'SELECT * FROM #Client '+CHAR(13)
+N'WHERE PageID > ' + RTRIM(@PageStart) + ' AND PageID <= ' + RTRIM(@PageStart+@PageSize)+CHAR(13)
+N' '
+N'SELECT COUNT(*) As TotalCount FROM #Client'
IF @DebugFlag = 1
PRINT '/*Paging SQL:*/'+CHAR(13)+@sSQL
EXEC SP_EXECUTESQL @sSQL
4. 高分求SQL分页的存储过程
ASP.Net中的DataGrid有内置分页功能,
但是它的默认的分页方式效率是很低的,特别是在数据量很大的时候,用它内置的分页功能几乎是不可能的事,因为它会把所有的数据从数据库读出来再进行分页,
这种只选取了一小部分而丢掉大部分的方法是不可去取的.
在最进的一个项目中因为一个管理页面要管理的数据量非常大,所以必须分页显示,并且不能用DataGrid的内置分页功能,于是自己实现分页.
下面介绍一下我在项目中用到的分页方法.
当然显示控件还是用DataGrid的,
因为数据绑定很方便^_^.
要保证不传输冗余的数据,那么必须在数据库中数据读取时实现分页,
数据库的分页操作可以放在存储过程中.
看了CSDN的一篇Blog中讲了一个百万级数据分页的存储过程的实现(
http://blog.csdn.net/wellknow/posts/55167.aspx
，他的这个方法可以根据不同情况进行适当的优化),
根据他的方法,这里实现一个简单的SQL语句来实现这里分页需要的存储过程。
create
procere
ListProct
(
@PageIndex
int,
--
分页后需要页的序号
@PageSize
int,
--
一页的大小
@ConditionSQL
–
查询条件的SQL语句
)
AS
…
具体代码就不写了(可以参考上面的链接).
具体的SQL语句如下:
Select
TOP
100
*
FROM
(select
*
from
proct
where
proctid<200000)
T
Where
T.proctid
NOT
IN
(Select
TOP
900
proctid
FROM
(select
proctid
from
proct
where
proctid<200000)
T1
orDER
BY
T1.proctid
asc)
orDER
BY
proctid
asc
这条语句的
从总的商品(30万)中取出proctid<200000(共20万),再按每页100的大小分页,然后取出第10页.
Public
DataTable
ListProct(int
pageIndex,
int
pageSize)
{
//ADO.net从数据库中取出数据的代码就略过^_^.
}
用上面的存储过程读出的数据在DataGrid里面分页,
必须把DataGrid的AllowPaging和AllowCustomPaging设置为true
protected
System.Web.UI.WebControls.DataGrid
ProctGrid;
ProctGrid.AllowPaging
=
true;
ProctGrid.AllowCustomPaging
=
true;
然后在设置要显示的一页的大小
ProctGrid.PageSize
=
100;
//
在显示的时候依据实际的数据显示。
设置一页大小后,如果要让DataGrid实际分出页数来,还必须设置
ProctGrid.VirtualItemCount
=
GetProctCount()
;
//
GetProctCount()
的功能是获取满足条件的产品数目,
这里的条件就是proctid<200000.
设置这项属性后,那么这个DataGrid的页数就是
VirtualItemCount/PageSize,
也就是PageCount的值.
不能对PageCount直接赋值,因为他是只读的属性.
5. 用Sql写一个分页查询的存储过程 表stuinfo主键stuno
随便你怎么填！
/****** 对象: StoredProcere [dbo].[usp_DataPagination_v1] 脚本日期: 09/03/2010 16:52:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_DataPagination_v1]
(
@tblName nvarchar(200), ----要显示的表或多个表的连接
@fldName nvarchar(500) = '*', ----要显示的字段列表
@pageSize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@pageCount int = 1 output, ----查询结果分页后的总页数
@Counts int = 1 output, ----查询到的记录数
@fldSort nvarchar(200), ----排序字段列表或条件
@Sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition nvarchar(2000) = null, ----查询条件,不需where
@ID nvarchar(150), ----主表的主键
@Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000) ----存放动态生成的SQL语句
Declare @strTmp nvarchar(max) ----存放取得查询结果总数的查询语句
Declare @strID nvarchar(1000) ----存放取得查询开头或结尾ID的查询语句
Declare @strSortType nvarchar(10) ----数据排序规则A
Declare @strFSortType nvarchar(10) ----数据排序规则B
Declare @SqlSelect nvarchar(50) ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50) ----对含有DISTINCT的总数查询进行SQL构造
if @Dist = 0
begin
set @SqlSelect = 'select '
set @SqlCounts = 'Count(*)'
end
else
begin
set @SqlSelect = 'select distinct '
set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
set @strFSortType=' ASC '
set @strSortType=' DESC '
end
else
begin
set @strFSortType=' DESC '
set @strSortType=' ASC '
end
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
set @sqlTmp = @fldName + ' From ' + @tblName
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
set @strID = ' From ' + @tblName
end
else
begin
set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition
set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition
set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition
end
----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts
--取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize
/**//**当前页大于总页数 取最后一页**/
if @page>@pageCount
set @page=@pageCount
--/*-----数据分页2分处理-------*/
declare @pageIndex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageIndex = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndex = @pageIndex + 1
else
set @lastcount = @pagesize
--//***显示分页
if @strCondition is null or @strCondition='' --没有设置显示条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strFSortType+')'
+' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' order by '+ @fldSort +' '+ @strSortType+')'
+' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
else --有查询条件
begin
if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理
begin
set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
+' Where (1>0) ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'
+' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType
end
else
begin
set @page = @pageIndex-@page+1 --后半部分数据处理
if @page <= 1 --最后一页数据显示
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
else
set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
+' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
+' where (1>0) '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+')'
+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
end
end
------返回查询结果-----
exec sp_executesql @strTmp
print @strTmp
SET NOCOUNT OFF
这已经是很成熟的了。。。。很精简。。你要分页的话基本都是输入这点参数。50万条数据测试5秒内
6. sql server 2005 存储过程分页怎么做
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
CREATE
proc
[dbo].[up_Page2005]
@TableName
varchar(50),
--表名
@Fields
varchar(5000)
=
'*',
--字段名(全部字段为*)
@OrderField
varchar(5000),
--排序字段(必须!支持多字段)
@sqlWhere
varchar(5000)
=
Null,--条件语句(不用加where)
@pageSize
int,
--每页多少条记录
@pageIndex
int
=
1
,
--指定当前为第几页
@TotalPage
int
output
--返回总页数
as
begin
Begin
Tran
--开始事务
Declare
@sql
nvarchar(4000);
Declare
@totalRecord
int;
--计算总记录数
if
(@SqlWhere=''
or
@sqlWhere=NULL)
set
@sql
=
'select
@totalRecord
=
count(*)
from
'
+
@TableName
else
set
@sql
=
'select
@totalRecord
=
count(*)
from
'
+
@TableName
+
'
with(nolock)
where
'
+
@sqlWhere
EXEC
sp_executesql
@sql,N'@totalRecord
int
OUTPUT',@totalRecord
OUTPUT--计算总记录数
--计算总页数
select
@TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
if
(@SqlWhere=''
or
@sqlWhere=NULL)
set
@sql
=
'Select
*
FROM
(select
ROW_NUMBER()
Over(order
by
'
+
@OrderField
+
')
as
rowId,'
+
@Fields
+
'
from
'
+
@TableName
else
set
@sql
=
'Select
*
FROM
(select
ROW_NUMBER()
Over(order
by
'
+
@OrderField
+
')
as
rowId,'
+
@Fields
+
'
from
'
+
@TableName
+
'
with(nolock)
where
'
+
@SqlWhere
--处理页数超出范围情况
if
@PageIndex<=0
Set
@pageIndex
=
1
if
@pageIndex>@TotalPage
Set
@pageIndex
=
@TotalPage
--处理开始点和结束点
Declare
@StartRecord
int
Declare
@EndRecord
int
set
@StartRecord
=
(@pageIndex-1)*@PageSize
+
1
set
@EndRecord
=
@StartRecord
+
@pageSize
-
1
--继续合成sql语句
set
@Sql
=
@Sql
+
')
as
t
where
rowId
between
'
+
Convert(varchar(50),@StartRecord)
+
'
and
'
+
Convert(varchar(50),@EndRecord)
print
@sql
Exec(@Sql)
---------------------------------------------------
If
@@Error
<>
0
Begin
RollBack
Tran
Return
-1
End
Else
Begin
Commit
Tran
Return
@totalRecord
---返回记录总数
End
end
7. sql存储过程定义多条件查询加分页
给你一个强大至极的分页存储过程（输入对应的参加即可），以此共勉，希望你也能进步：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Pagination]
@Page int = 1, -- 当前页码
@PageSize int = 10, -- 每页记录条数(页面大小)
@Table nvarchar(500), -- 表名或视图名，甚至可以是嵌套SQL：(Select * From Tab Where ID>1000) Tab
@Field nvarchar(800) = '*', -- 返回记录集字段名，","隔开，默认是"*"
@OrderBy nvarchar(100) = 'ID ASC', -- 排序规则
@Filter nvarchar(500), -- 过滤条件
@MaxPage smallint output, -- 执行结果 -1 error, 0 false, maxpage true
@TotalRow int output, -- 记录总数 /* 2007-07-12 22:11:00 update */
@Descript varchar(100) output -- 结果描述
AS
BEGIN
-- =============================================
-- Author: Jimmy.Yu
-- Create date: 2007-5-11
-- Description: SQL 2005 以上版本 通用分页存储过程
-- =============================================
Set ROWCOUNT @PageSize;
Set @Descript = 'successful';
-------------------参数检测----------------
IF LEN(RTRIM(LTRIM(@Table))) !> 0
Begin
Set @MaxPage = 0;
Set @Descript = 'table name is empty';
Return;
End
IF LEN(RTRIM(LTRIM(@OrderBy))) !> 0
Begin
Set @MaxPage = 0;
Set @Descript = 'order is empty';
Return;
End
IF ISNULL(@PageSize,0) <= 0
Begin
Set @MaxPage = 0;
Set @Descript = 'page size error';
Return;
End
IF ISNULL(@Page,0) <= 0
Begin
Set @MaxPage = 0;
Set @Descript = 'page error';
Return;
End
-------------------检测结束----------------
Begin Try
-- 整合SQL
Declare @SQL nvarchar(4000), @Portion nvarchar(4000);
Set @Portion = ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ROWNUM FROM ' + @Table;
Set @Portion = @Portion + (CASE WHEN LEN(@Filter) >= 1 THEN (' Where ' + @Filter + ') AS tab') ELSE (') AS tab') END);
Set @SQL = 'Select TOP(' + CAST(@PageSize AS nvarchar(8)) + ') ' + @Field + ' FROM (Select ' + @Field + ',' + @Portion;
Set @SQL = @SQL + ' Where tab.ROWNUM > ' + CAST((@Page-1)*@PageSize AS nvarchar(8));
-- 执行SQL, 取当前页记录集
Execute(@SQL);
--------------------------------------------------------------------
-- 整合SQL
Set @SQL = 'Set @Rows = (Select MAX(ROWNUM) FROM (Select' + @Portion + ')';
-- 执行SQL, 取最大页码
Execute sp_executesql @SQL, N'@Rows int output', @TotalRow output;
Set @MaxPage = (CASE WHEN (@TotalRow % @PageSize)<>0 THEN (@TotalRow / @PageSize + 1) ELSE (@TotalRow / @PageSize) END);
End Try
Begin Catch
-- 捕捉错误
Set @MaxPage = -1;
Set @Descript = 'error line: ' + CAST(ERROR_LINE() AS varchar(8)) + ', error number: ' + CAST(ERROR_NUMBER() AS varchar(8)) + ', error message: ' + ERROR_MESSAGE();
Return;
End Catch;
-- 执行成功
Return;
END
8. sql server 分页存储过程
CREATE PROCEDURE pagination
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + ']
where [' + @fldName + ']' + @strTmp + '(['+ @fldName + '])
from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + ']
from [' + @tblName + ']' + @strOrder + ')
as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + ']
where [' + @fldName + ']' + @strTmp + '([' + @fldName + '])
from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + ']
from [' + @tblName + ']
where ' + @strWhere + ' ' + @strOrder + ')
as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
GO
/*
-- 需要传递的参数
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
-- 调用测试
exec pagination @tblName='jobs', @strGetFields='job_id,job_desc,min_lvl,max_lvl',
@fldName='job_id',@PageSize=3,@PageIndex=1,
@doCount=0,@OrderType=1,@strWhere=''
*/
============================================
CREATE PROC SP_PageList
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
--用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@RecordCount int OUTPUT, --总记录数
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @RecordCount = @PageCount
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
END
GO