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
⑵ 如何利用 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
⑶ 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
⑷ 怎样调 sql server 2005 存储过程分页
首先建立好SQL 分页存储过程，使用的时候在数据库运行一下就可以了。
⑸ SQL Server 分页 查询语句
四种方式实现SQLServer 分页查询
SQLServer 的数据分页:
假设现在有这样的一张表：
CREATE TABLE test
(
id int primary key not null identity,
names varchar(20)
)
然后向里面插入大约1000条数据，进行分页测试
假设页数是10，现在要拿出第5页的内容，查询语句如下：
--10代表分页的大小
select top 10 *
from test
where id not in
(
--40是这么计算出来的：10*(5-1)
select top 40 id from test order by id
)
order by id
原理：需要拿出数据库的第5页，就是40-50条记录。首先拿出数据库中的前40条记录的id值，然后再拿出剩余部分的前10条元素
第二种方法：
还是以上面的结果为例，采用另外的一种方法
--数据的意思和上面提及的一样
select top 10 *
from test
where id >
(
select isnull(max(id),0)
from
(
select top 40 id from test order by id
) A
)
order by id
原理：先查询前40条记录，然后获得其最id值，如果id值为null的，那么就返回0
然后查询id值大于前40条记录的最大id值的记录。
这个查询有一个条件，就是id必须是int类型的。
第三种方法：
select top 10 *
from
(
select row_number() over(order by id) as rownumber,* from test
) A
where rownumber > 40
原理：先把表中的所有数据都按照一个rowNumber进行排序，然后查询rownuber大于40的前十条记录
这种方法和oracle中的一种分页方式类似，不过只支持2005版本以上的
第四种：
存储过程查询
创建存储过程
alter procere pageDemo
@pageSize int,
@page int
AS
declare @temp int
set @temp=@pageSize*(@page - 1)
begin
select top (select @pageSize) * from test where id not in (select top (select @temp) id from test) order by id
end
执行存储过程
exec 10,5
⑹ SQL分页的几种方法
方法一 使用offset fetch next（2012版本及以上版本才可以使用）
方法二 使用row_number()函数
利用row_number() over(order by id desc)函数计算出行数，选定相应的行数返回即可（2005版本以上才可以使用）
使用存储过程封装
⑺ sql存储过程分页
因为这时动态字符串拼接,如果不转换会报错的，会把+当作加法符号
⑻ Sqlserver2005的存储过程分页转换成Sqlserver 2000的
create proc GetCoumterPage
@currentIndex int,--当前页
@pagesize int,--显示的行数
@pageCount int output--总页码数
as
declare
@rowcount int
set @rowcount=(select count(*) from Singles)
if(@rowcount%@pagesize>0)
set @pagecount =(@rowcount/@pagesize)+1
else
set @pagecount=@rowcount/@pagesize
select identity(int,1,1) AS rankid,id INTO #t
From Singles
select*from
(
select rankid,a.id,title,[content],operating,photo,pdf,remark from Singles a inner join #t b on a.id=b.id
)
as SinglesInfo where rankid >@currentIndex*@pagesize and rankid<=@currentIndex*@pagesize+@pagesize
⑼ 求SQL2005高性能存储过程分页代码，该怎么处理
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;
}
⑽ SQL里存储过程分页问题！急！急！
CREATE PROCEDURE [Zhzuo_GetItemsPage]
@PageIndex INT, /*@PageIndex从计数,0为第一页*/
@PageSize INT, /*页面大小*/
@RecordCount INT OUT, /*总记录数*/
@PageCount INT OUT /*页数*/
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
'ProctID,Name FROM Proction.Proct ORDER BY ProctID DESC'
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
'ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T ORDER BY ProctID DESC'
END
ELSE
BEGIN
SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
'ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T ORDER BY ProctID DESC'
END
END
/*执行*/
EXEC (@SQLSTR)
以上存储过程对页数进行判断，如果是第一页或最后一页，进行特殊处理。其他情况使用2次TOP翻转。其中排序条件为ProctID倒序。最后通过EXECUTE执行SQL字符串拼串。
2．SQL SERVER 2005中的TOP分页
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
/*基于SQL SERVER 2005 */
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SELECT TOP(@PageSize) ProctID,Name FROM Proction.Proct ORDER BY ProctID DESC
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SELECT * FROM ( SELECT TOP(@TOPCOUNT) ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T
ORDER BY ProctID DESC
END
ELSE
BEGIN
SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T
ORDER BY ProctID DESC
END
END
以上存储过程是使用2005的TOP (表达式) 新功能，避免了字符串拼串，使结构化查询语言变得简洁。实现的为同样的功能。
3．SQL SERVER 2005中的新分页
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基于SQL SERVER 2005 */
SELECT SerialNumber,ProctID,Name FROM
(SELECT ProctID,Name,ROW_NUMBER() OVER (ORDER BY ProctID DESC) AS SerialNumber FROM Proction.Proct ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
第三个存储过程使用2005下新的功能，实现的分页存储过程功能更加简单明了，而且更加容易理解。注意这里的ProctID为主键,根据ProctID进行排序生成ROW_NUMBER，通过ROW_NUMBER来确定具体的页数。
通过对三个分页存储过程的比较，可见SQL SERVER 的TSQL 语言对分页功能的支持进步不少。使分页实现趋向于简单化。