在这篇文章中，我们将带领您了解SqlServer分页存储过程的全貌，包括sqlserver存储过程分页查询的相关情况。同时，我们还将为您介绍有关mmssqlserver中三种分页存储过程代码、MSSq
在这篇文章中，我们将带领您了解SqlServer 分页存储过程的全貌，包括sqlserver存储过程分页查询的相关情况。同时，我们还将为您介绍有关mmssqlserver中三种分页存储过程代码、MS SqlServer 海量数据分页存储过程收集、MS SqlServer海量数据分页存储过程收集、sql server 2005分页存储过程和sql server 2000分页存储过程的知识，以帮助您更好地理解这个主题。
本文目录一览：- SqlServer 分页存储过程（sqlserver存储过程分页查询）
- mmssqlserver中三种分页存储过程代码
- MS SqlServer 海量数据分页存储过程收集
- MS SqlServer海量数据分页存储过程收集
- sql server 2005分页存储过程和sql server 2000分页存储过程
SqlServer 分页存储过程（sqlserver存储过程分页查询）
create proc [dbo].[proc_Opinion_BaseInfo] @TableName varchar(4000),@PkField varchar(100),@PageIndex int=1,@PageSize int=10,@sqlWhere nvarchar(4000),@RowCount bigint output,@PageCount bigint output as if(@sqlWhere='1') set @sqlWhere = '1=1' declare @sql nvarchar(4000),@start int,@end int set @sql='select * from (select Row_NUMBER() OVER(order by '+@PkField+' desc) rowId,* from '+@TableName+' where '+@sqlWhere set @start = (@PageIndex-1)*@PageSize+1 set @end = @start+@PageSize-1 set @sql = @sql + ') t where rowId between '+CAST(@start as varchar(20))+' and ' +CAST(@end as varchar(20)) exec (@sql) set @sql = 'select @RowCount=count(1) from '+@TableName+' where '+@sqlWhere exec sp_executesql @sql,N'@RowCount bigint OUTPUT',@RowCount OUTPUT if(@RowCount%@PageSize=0) begin set @PageCount = @RowCount / @PageSize end else begin set @PageCount = @RowCount / @PageSize +1 end
mmssqlserver中三种分页存储过程代码
本文章介绍了关于在mmssqlserver中三种分页存储过程实现代码，有需要的朋友可以参考一下下哈，好了费话不说多了我们来看看吧。
--根据max(min)id
| 代码如下 | 复制代码 |
| CREATE PROC [dbo].[proc__id] @pageindex int=1,--当前页数 @pagesize int=10,--每页大小 @tablename VARCHAR(50)='''',--表名 @fields VARCHAR(1000)='''',--查询的字段集合 @keyid VARCHAR(50)='''',--主键 @condition NVARCHAR(1000)='''',--查询条件 @orderstr VARCHAR(500),--排序条件 @totalRecord BIGINT OUTPUT--总记录数 AS IF ISNULL(@orderstr,N'''')=N'''' SET @orderstr=N'' ORDER BY ''+@keyid+N'' DESC '' IF ISNULL(@fields,N'''')=N'''' SET @fields=N''*'' IF ISNULL(@condition,N'''')=N'''' SET @condition=N''1=1'' DECLARE @sql NVARCHAR(4000) --IF(@totalRecord IS NULL) --BEGIN SET @sql=N''SELECT @totalRecord=COUNT(*)'' +N'' FROM ''+@tablename +N'' WHERE ''+@condition EXEC sp_executesql @sql,N''@totalRecord INT OUTPUT'',@totalRecord OUTPUT --END IF(@pageindex=1) BEGIN SET @sql=N''SELECT TOP ''+STR(@pagesize)+N'' ''+@fields+N'' FROM ''+@tablename+N'' WHERE ''+@condition+N'' ''+@orderstr EXEC(@sql) END ELSE BEGIN DECLARE @operatestr CHAR(3),@comparestr CHAR(1) SET @operatestr=''MAX'' SET @comparestr=''>'' IF(@orderstr'''') BEGIN IF(CHARINDEX(''desc'',LOWER(@orderstr))0) BEGIN SET @operatestr=''MIN'' SET @comparestr='' END END SET @sql=N''SELECT top ''+STR(@pagesize)+N'' ''+@fields+N'' FROM ''+@tablename+N'' WHERE ''+@keyid+@comparestr +N''(SELECT ''+@operatestr+N''(''+@keyid+N'') FROM ''+@tablename+N'' WHERE ''+@keyid +N'' IN (SELECT TOP ''+STR((@pageindex-1)*@pagesize)+N'' ''+@keyid+N'' FROM ''+@tablename+N'' WHERE '' +@condition+N'' ''+@orderstr+N'')) AND ''+@condition+N'' ''+@orderstr EXEC(@sql) END GO |
|
--根据ROW_NUMBER() OVER
| 代码如下 | 复制代码 |
| CREATE PROC [dbo].[proc_select_page_row] @pageindex INT=1,--当前页数 @pagesize INT=10,--每页大小 @tablename VARCHAR(50)='''',--表名 @fields VARCHAR(1000)=''*'',--查询的字段集合 @keyid VARCHAR(50)='''',--主键 @condition NVARCHAR(1000)='''',--查询条件 @orderstr VARCHAR(500),--排序条件 @totalRecord BIGINT OUTPUT--总记录数 AS IF ISNULL(@orderstr,N'''')=N'''' SET @orderstr=N'' ORDER BY ''+@keyid+N'' DESC '' IF ISNULL(@fields,N'''')=N'''' SET @fields=N''*'' IF ISNULL(@condition,N'''')=N'''' SET @condition=N''1=1'' DECLARE @sql NVARCHAR(4000) -- IF @totalRecord IS NULL -- BEGIN SET @sql=N''SELECT @totalRecord=COUNT(*)'' +N'' FROM ''+@tablename +N'' WHERE ''+@condition EXEC sp_executesql @sql,N''@totalRecord bigint OUTPUT'',@totalRecord OUTPUT --END IF(@pageindex=1) BEGIN SET @sql=N''SELECT TOP ''+STR(@pagesize)+N'' ''+@fields+N'' FROM ''+@tablename+N'' WHERE ''+@condition+N'' ''+@orderstr EXEC(@sql) END ELSE BEGIN DECLARE @StartRecord INT SET @StartRecord = (@pageindex-1)*@pagesize + 1 SET @sql=N''SELECT * FROM (SELECT ROW_NUMBER() OVER (''+ @orderstr +N'') AS rowId,''+@fields+N'' FROM ''+ @tablename+N'') AS T WHERE rowId>=''+STR(@StartRecord)+N'' and rowId EXEC(@sql) END GO |
|
--根据TOP ID
| 代码如下 | 复制代码 |
| CREATE PROC [dbo].[proc_select_page_top] @pageindex INT=1,--当前页数 @pagesize INT=10,--每页大小 @tablename VARCHAR(50)='''',--表名 @fields VARCHAR(1000)='''',--查询的字段集合 @keyid VARCHAR(50)='''',--主键 @condition NVARCHAR(1000)='''',--查询条件 @orderstr VARCHAR(500),--排序条件 @totalRecord BIGINT OUTPUT--总记录数 AS IF ISNULL(@orderstr,N'''')=N'''' SET @orderstr=N'' ORDER BY ''+@keyid+N'' DESC '' IF ISNULL(@fields,N'''')=N'''' SET @fields=N''*'' IF ISNULL(@condition,N'''')=N'''' SET @condition=N''1=1'' DECLARE @sql NVARCHAR(4000) --IF(@totalRecord IS NULL) --BEGIN SET @sql=N''SELECT @totalRecord=COUNT(*)'' +N'' FROM ''+@tablename +N'' WHERE ''+@condition EXEC sp_executesql @sql,N''@totalRecord INT OUTPUT'',@totalRecord OUTPUT --END IF(@pageindex=1) BEGIN SET @sql=N''SELECT TOP ''+STR(@pagesize)+N'' ''+@fields+N'' FROM ''+@tablename+N'' WHERE ''+@condition+N'' ''+@orderstr EXEC(@sql) END ELSE BEGIN SET @sql=N''SELECT TOP ''+STR(@pagesize)+N'' ''+@fields+N'' FROM ''+@tablename+N'' WHERE ''+@keyid +N'' NOT IN(SELECT TOP ''+STR((@pageindex-1)*@pagesize)+N'' ''+@keyid+N'' FROM '' +@tablename+N'' WHERE ''+@condition+N'' ''+@orderstr+N'') AND ''+@condition+N'' ''+@orderstr EXEC(@sql) END GO |
|
MS SqlServer 海量数据分页存储过程收集
CREATE PROC [dbo].[Mypage] @tableName SYSNAME,-- 表名
@keyField NVARCHAR(1000),-- 主键,多个主键有逗号分隔开
@pageIndex INT=1,-- 要显示的页码
@pageSize INT=10,-- 每页的大小(记录数)
@fieldName NVARCHAR(1000)='''',-- 要显示的字段列表
@sortName NVARCHAR(1000)='''',-- 以逗号分隔的排序字段列表(如:id asc,Name asc)用于指定排序顺序
@strWhere NVARCHAR(1000)='''',-- 查询条件
@recordCount INT=0 OUTPUT,-- 总记录数
@pageCount INT=0 OUTPUT -- 总页数
AS
SET NOCOUNT ON
--检查对象是否有效
IF Object_id(@tableName) IS NULL
BEGIN
RAISERROR(N''对象"%s"不存在'',1,16,@tableName)
RETURN
END
IF Objectproperty(Object_id(@tableName), N''IsTable'') = 0
AND Objectproperty(Object_id(@tableName), N''IsView'') = 0
AND Objectproperty(Object_id(@tableName), N''IsTableFunction'') = 0
BEGIN
RAISERROR(N''"%s"不是表,视图或者表值函数'',1,16,@tableName)
RETURN
END
--分页字段检查
IF Isnull(@keyField, N'''') = ''''
BEGIN
RAISERROR(N''分页处理需要主键(或者惟一键)'',1,16)
RETURN
END
--?其它参数检查及规范
IF Isnull(@pageIndex, 0) < 1
SET @pageIndex=1
IF Isnull(@pageSize, 0) < 1
SET @pageSize=10
IF Isnull(@fieldName, N'''') = N''''
SET @fieldName=N''*''
IF Isnull(@sortName, N'''') = N''''
SET @sortName=N''''
ELSE
SET @sortName=N''ORDER BY '' + Ltrim(@sortName)
IF Isnull(@strWhere, N'''') = N''''
SET @strWhere=N''''
ELSE
SET @strWhere=N''WHERE ('' + @strWhere + N'')''
--如果为NULL值,则计算总页数(可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算,对于不想计算总页数,可以给@pageCount赋值)
IF @pageCount IS NULL
BEGIN
DECLARE @sql NVARCHAR(4000)
SET @sql=N''SELECT @recordCount=COUNT(*)'' + N'' FROM ''
+ @tableName + N'' '' + @strWhere
EXEC Sp_executesql
@sql,
N''@recordCount int OUTPUT'',
@recordCount OUTPUT
SET @pageCount=Ceiling(@recordCount * 1.0 / @pageSize)
END
--计算分页显示 top N 值
DECLARE @TopN VARCHAR(20),
@TopN1 VARCHAR(20)
SELECT @TopN = @pageSize,
@TopN1 = @pageIndex * @pageSize
--第一页直接显示
IF @pageIndex = 1
EXEC(N''SELECT TOP ''+@TopN +N'' ''+@fieldName +N'' FROM ''+@tableName +N'' ''+@strWhere +N'' ''+@sortName)
ELSE
BEGIN
--生成主键(唯一键)处理条件
DECLARE @Where1 NVARCHAR(4000),
@s NVARCHAR(1000)
SELECT @Where1 = N'''',
@s = @keyField
WHILE Charindex(N'','', @s) > 0
SELECT @s = Stuff(@s, 1, Charindex(N'','', @s), N''''),
@Where1 = @Where1 + N'' AND a.''
+ LEFT(@s, Charindex(N'','', @s)-1) + N''=''
+ LEFT(@s, Charindex(N'','', @s)-1)
SELECT @Where1 = Stuff(@Where1 + N'' AND a.'' + @s + N''='' + @s, 1, 5, N''''),
@TopN = @TopN1 - @pageSize
EXEC(N''SET ROWCOUNT ''+@TopN1 +N'' SELECT ''+@keyField +N'' INTO # FROM ''+@tableName +N'' ''+@strWhere +N'' ''+@sortName +N'' SET ROWCOUNT ''+@TopN +N'' DELETE FROM #'' +N'' SELECT ''+@fieldName +N'' FROM ''+@tableName +N'' a WHERE EXISTS(SELECT * FROM # WHERE ''+@Where1 +N'') ''+@sortName)
ENDprotected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridViewData();
}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
#region 如果是绑定数据行,鼠标滑过变色
if (e.Row.RowType == DataControlRowType.DataRow)
{
//鼠标经过时，行背景色变
e.Row.Attributes["onmouseover"] = "ItemOver(this)";
//鼠标经过行变成手形
//e.Row.Style.Add("cursor", "hand");
}
#endregion
#region 鼠标单击行，打开窗口同时传出参数ID
//string ID = String.Empty;
//for (int i = 0; i < grvTestTable.Rows.Count; i++)
//{
// ID = grvTestTable.DataKeys[i].Value.ToString();
// grvTestTable.Rows[i].Attributes.Add("onclick", "window.open(''Detail.aspx?PKID="+ID+" '')");
//}
#endregion
}
private void BindGridViewData()
{
string strCondition = "";
MSCL.PageHelper wp = new MSCL.PageHelper();
wp.TableName = "AFM_EmailMessage";
wp.KeyField = "EID";
wp.SortName = "EID";
wp.Condition = strCondition;
wp.CurrentPageIndex = AspNetPager1.CurrentPageIndex;
wp.PageSize = AspNetPager1.PageSize;//=PageSize;
DataTable dt = wp.GetDataTableMyPage();
AspNetPager1.RecordCount = wp.RecordCount;
grvAFM_EmailMessage.DataSource = dt;
grvAFM_EmailMessage.DataBind();
AspNetPager1.CustomInfoHTML = " 共<font color=''#FF8000''><b>" + wp.RecordCount.ToString() + "</b></font>条记录/";
AspNetPager1.CustomInfoHTML += " <font color=#FF8000''><b>" + wp.PageCount.ToString() + "</b></font>页";
AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>页";
/*
MSCL.PageHelper p = new PageHelper();
p.CurrentPageIndex = AspNetPager1.CurrentPageIndex;
p.FieldsName = "*";
p.KeyField = "d_id";
p.SortName = "d_id asc";
p.TableName = "testtable";
p.EndCondition = "count(*)";
p.PageSize = AspNetPager1.PageSize;
DataTable dt = p.QueryPagination();
AspNetPager1.RecordCount = p.RecordCount;
Repeater1.DataSource = dt;
Repeater1.DataBind();
AspNetPager1.CustomInfoHTML = " 共<b>" + p.RecordCount + "</b>条记录/";
AspNetPager1.CustomInfoHTML += "<b>" + p.PageCount + "</b>页";
AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + p.CurrentPageIndex + "</b></font>页";
*/
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
BindGridViewData();
}CREATE PROC P_viewPage
/*
敬告：适用于单一主键或存在唯一值列的表或视图
*/
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*
@PrimaryKey VARCHAR(100), --单一主键或唯一值键
@Where VARCHAR(2000), --查询条件 不含''where''字符，如id>10 and len(userid)>9
@Order VARCHAR(1000), --排序 不含''order by''字符，如id asc,userid desc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT, --记录总数 0:会返回总记录
@PageSize INT, --每页输出的记录数
@PageIndex INT, --当前页数
@TotalCount INT OUTPUT, --记返回总记录
@TotalPageCount INT OUTPUT --返回总页数
AS
SET NOCOUNT ON
IF ISNULL(@TotalCount,'''') = '''' SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),'' '','''')
WHILE CHARINDEX('', '',@Order) > 0 OR CHARINDEX('' ,'',@Order) > 0
BEGIN
SET @Order = REPLACE(@Order,'', '','','')
SET @Order = REPLACE(@Order,'' ,'','','')
END
IF ISNULL(@TableName,'''') = '''' OR ISNULL(@FieldList,'''') = ''''
OR ISNULL(@PrimaryKey,'''') = ''''
OR @SortType < 1 OR @SortType >3
OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN
PRINT(''ERR_00'')
RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!='' ASC'' AND UPPER(RIGHT(@Order,5))!='' DESC'')
BEGIN PRINT(''ERR_02'') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)
IF ISNULL(@where,'''') = ''''
BEGIN
SET @new_where1 = '' ''
SET @new_where2 = '' WHERE ''
END
ELSE
BEGIN
SET @new_where1 = '' WHERE '' + @where
SET @new_where2 = '' WHERE '' + @where + '' AND ''
END
IF ISNULL(@order,'''') = '''' OR @SortType = 1 OR @SortType = 2
BEGIN
IF @SortType = 1
BEGIN
SET @new_order1 = '' ORDER BY '' + @PrimaryKey + '' ASC''
SET @new_order2 = '' ORDER BY '' + @PrimaryKey + '' DESC''
END
IF @SortType = 2
BEGIN
SET @new_order1 = '' ORDER BY '' + @PrimaryKey + '' DESC''
SET @new_order2 = '' ORDER BY '' + @PrimaryKey + '' ASC''
END
END
ELSE
BEGIN
SET @new_order1 = '' ORDER BY '' + @Order
END
IF @SortType = 3 AND CHARINDEX('',''+@PrimaryKey+'' '','',''+@Order)>0
BEGIN
SET @new_order1 = '' ORDER BY '' + @Order
SET @new_order2 = @Order + '',''
SET @new_order2 = REPLACE(REPLACE(@new_order2,''ASC,'',''{ASC},''),''DESC,'',''{DESC},'')
SET @new_order2 = REPLACE(REPLACE(@new_order2,''{ASC},'',''DESC,''),''{DESC},'',''ASC,'')
SET @new_order2 = '' ORDER BY '' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> ''*''
BEGIN
SET @new_order3 = REPLACE(REPLACE(@Order + '','',''ASC,'','',''),''DESC,'','','')
SET @FieldList = '','' + @FieldList
WHILE CHARINDEX('','',@new_order3)>0
BEGIN
IF CHARINDEX(SUBSTRING('',''+@new_order3,1,CHARINDEX('','',@new_order3)),'',''+@FieldList+'','')>0
BEGIN
SET @FieldList =
@FieldList + '','' + SUBSTRING(@new_order3,1,CHARINDEX('','',@new_order3))
END
SET @new_order3 =
SUBSTRING(@new_order3,CHARINDEX('','',@new_order3)+1,LEN(@new_order3))
END
SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
END
END
SET @SqlCount = ''SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/''
+ CAST(@PageSize AS VARCHAR)+'') FROM '' + @TableName + @new_where1
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N''@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT'',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
ELSE
BEGIN
SELECT @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一页数据
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ''
+ @TableName + @new_where1 + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM (''
+ ''SELECT TOP '' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ '' '' + @FieldList + '' FROM ''
+ @TableName + @new_where1 + @new_order2 + '' ) AS TMP ''
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ''
+ @TableName + @new_where2 + @PrimaryKey + '' > ''
+ ''(SELECT MAX('' + @PrimaryKey + '') FROM (SELECT TOP ''
+ STR(@PageSize*(@PageIndex-1)) + '' '' + @PrimaryKey
+ '' FROM '' + @TableName
+ @new_where1 + @new_order1 +'' ) AS TMP) ''+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM (''
+ ''SELECT TOP '' + STR(@PageSize) + '' ''
+ @FieldList + '' FROM ''
+ @TableName + @new_where2 + @PrimaryKey + '' < ''
+ ''(SELECT MIN('' + @PrimaryKey + '') FROM (SELECT TOP ''
+ STR(@TotalCount-@PageSize*@PageIndex) + '' '' + @PrimaryKey
+ '' FROM '' + @TableName
+ @new_where1 + @new_order2 +'' ) AS TMP) ''+ @new_order2
+ '' ) AS TMP '' + @new_order1
END
END
IF @SortType = 2 --仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ''
+ @TableName + @new_where2 + @PrimaryKey + '' < ''
+ ''(SELECT MIN('' + @PrimaryKey + '') FROM (SELECT TOP ''
+ STR(@PageSize*(@PageIndex-1)) + '' '' + @PrimaryKey
+'' FROM ''+ @TableName
+ @new_where1 + @new_order1 + '') AS TMP) ''+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM (''
+ ''SELECT TOP '' + STR(@PageSize) + '' ''
+ @FieldList + '' FROM ''
+ @TableName + @new_where2 + @PrimaryKey + '' > ''
+ ''(SELECT MAX('' + @PrimaryKey + '') FROM (SELECT TOP ''
+ STR(@TotalCount-@PageSize*@PageIndex) + '' '' + @PrimaryKey
+ '' FROM '' + @TableName
+ @new_where1 + @new_order2 +'' ) AS TMP) ''+ @new_order2
+ '' ) AS TMP '' + @new_order1
END
END
IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IF CHARINDEX('','' + @PrimaryKey + '' '','','' + @Order) = 0
BEGIN PRINT(''ERR_02'') RETURN END
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ( ''
+ ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ( ''
+ '' SELECT TOP '' + STR(@PageSize*@PageIndex) + '' '' + @FieldList
+ '' FROM '' + @TableName + @new_where1 + @new_order1 + '' ) AS TMP ''
+ @new_order2 + '' ) AS TMP '' + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ( ''
+ ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ( ''
+ '' SELECT TOP '' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + '' '' + @FieldList
+ '' FROM '' + @TableName + @new_where1 + @new_order2 + '' ) AS TMP ''
+ @new_order1 + '' ) AS TMP '' + @new_order1
END
END
END
PRINT(@Sql)
EXEC(@Sql)
GO--利用ROW_NUMBER快速分页
ALTER PROCEDURE [dbo].[pr_Pagination]
@TableName varchar(500), --要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id)
@Fields varchar(500), --表中的字段，可以使用*代替
@OrderField varchar(500), --要排序的字段
@SqlWhere varchar(max), --WHERE子句
@PageSize int, --分页的大小
@PageIndex int, --要显示的页的索引
@TotalRecord int output,--记录总数
@TotalPage int output, --页的总数
@WholeContion varchar (1000) --存储过程中的条件
as
begin
--Begin Tran
Declare @sql nvarchar(4000);
Declare @Record int; --记录总数
--利用WHERE子句进行过滤
if (isnumeric(@WholeContion)=1 )
begin
set @sql = ''select @Record = count(*) from '' + @TableName + '' where 1=1 '' + @SqlWhere
EXEC sp_executesql @sql,N''@Record int OUTPUT'',@Record OUTPUT
if (CAST(@WholeContion as int ) < @Record )
begin
set @Record = CAST(@WholeContion as int )
end
end
else
begin
set @sql = ''select @Record = '' + @WholeContion + '' from '' + @TableName + '' where 1=1 '' + @SqlWhere
--执行sql语句得到记录总数
EXEC sp_executesql @sql,N''@Record int OUTPUT'',@Record OUTPUT
end
select @TotalPage=CEILING((@Record+0.0)/@PageSize)
select @TotalRecord=@Record
--select @TotalRecord
--select @TotalPage
--根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序
set @sql = ''select * from (select ROW_NUMBER() over(order by '' + @OrderField + '') as rowId,'' + @Fields + '' from '' + @TableName + '' where 1=1 '' + @SqlWhere
--确保当前页的索引在合理的范围之内
if @PageIndex<=0
Set @PageIndex = 1
--得到当前页在整个结果集中准确的ROW_NUMBER值
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@PageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
--输出当前页中的数据
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
--End
--Else
--Begin
--Commit Tran
--End
end
MS SqlServer海量数据分页存储过程收集
CREATE PROC [dbo].[Mypage] @tableName SYSNAME,-- 表名
@keyField NVARCHAR(1000),-- 主键,多个主键有逗号分隔开
@pageIndex INT=1,-- 要显示的页码
@pageSize INT=10,-- 每页的大小(记录数)
@fieldName NVARCHAR(1000)='''',-- 要显示的字段列表
@sortName NVARCHAR(1000)='''',-- 以逗号分隔的排序字段列表(如:id asc,Name asc)用于指定排序顺序
@strWhere NVARCHAR(1000)='''',-- 查询条件
@recordCount INT=0 OUTPUT,-- 总记录数
@pageCount INT=0 OUTPUT -- 总页数
AS
SET NOCOUNT ON
--检查对象是否有效
IF Object_id(@tableName) IS NULL
BEGIN
RAISERROR(N''对象"%s"不存在'',1,16,@tableName)
RETURN
END
IF Objectproperty(Object_id(@tableName), N''IsTable'') = 0
AND Objectproperty(Object_id(@tableName), N''IsView'') = 0
AND Objectproperty(Object_id(@tableName), N''IsTableFunction'') = 0
BEGIN
RAISERROR(N''"%s"不是表,视图或者表值函数'',1,16,@tableName)
RETURN
END
--分页字段检查
IF Isnull(@keyField, N'''') = ''''
BEGIN
RAISERROR(N''分页处理需要主键(或者惟一键)'',1,16)
RETURN
END
--?其它参数检查及规范
IF Isnull(@pageIndex, 0) < 1
SET @pageIndex=1
IF Isnull(@pageSize, 0) < 1
SET @pageSize=10
IF Isnull(@fieldName, N'''') = N''''
SET @fieldName=N''*''
IF Isnull(@sortName, N'''') = N''''
SET @sortName=N''''
ELSE
SET @sortName=N''ORDER BY '' + Ltrim(@sortName)
IF Isnull(@strWhere, N'''') = N''''
SET @strWhere=N''''
ELSE
SET @strWhere=N''WHERE ('' + @strWhere + N'')''
--如果为NULL值,则计算总页数(可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算,对于不想计算总页数,可以给@pageCount赋值)
IF @pageCount IS NULL
BEGIN
DECLARE @sql NVARCHAR(4000)
SET @sql=N''SELECT @recordCount=COUNT(*)'' + N'' FROM ''
+ @tableName + N'' '' + @strWhere
EXEC Sp_executesql
@sql,
N''@recordCount int OUTPUT'',
@recordCount OUTPUT
SET @pageCount=Ceiling(@recordCount * 1.0 / @pageSize)
END
--计算分页显示 top N 值
DECLARE @TopN VARCHAR(20),
@TopN1 VARCHAR(20)
SELECT @TopN = @pageSize,
@TopN1 = @pageIndex * @pageSize
--第一页直接显示
IF @pageIndex = 1
EXEC(N''SELECT TOP ''+@TopN +N'' ''+@fieldName +N'' FROM ''+@tableName +N'' ''+@strWhere +N'' ''+@sortName)
ELSE
BEGIN
--生成主键(唯一键)处理条件
DECLARE @Where1 NVARCHAR(4000),
@s NVARCHAR(1000)
SELECT @Where1 = N'''',
@s = @keyField
WHILE Charindex(N'','', @s) > 0
SELECT @s = Stuff(@s, 1, Charindex(N'','', @s), N''''),
@Where1 = @Where1 + N'' AND a.''
+ LEFT(@s, Charindex(N'','', @s)-1) + N''=''
+ LEFT(@s, Charindex(N'','', @s)-1)
SELECT @Where1 = Stuff(@Where1 + N'' AND a.'' + @s + N''='' + @s, 1, 5, N''''),
@TopN = @TopN1 - @pageSize
EXEC(N''SET ROWCOUNT ''+@TopN1 +N'' SELECT ''+@keyField +N'' INTO # FROM ''+@tableName +N'' ''+@strWhere +N'' ''+@sortName +N'' SET ROWCOUNT ''+@TopN +N'' DELETE FROM #'' +N'' SELECT ''+@fieldName +N'' FROM ''+@tableName +N'' a WHERE EXISTS(SELECT * FROM # WHERE ''+@Where1 +N'') ''+@sortName)
ENDprotected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridViewData();
}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
#region 如果是绑定数据行,鼠标滑过变色
if (e.Row.RowType == DataControlRowType.DataRow)
{
//鼠标经过时，行背景色变
e.Row.Attributes["onmouseover"] = "ItemOver(this)";
//鼠标经过行变成手形
//e.Row.Style.Add("cursor", "hand");
}
#endregion
#region 鼠标单击行，打开窗口同时传出参数ID
//string ID = String.Empty;
//for (int i = 0; i < grvTestTable.Rows.Count; i++)
//{
// ID = grvTestTable.DataKeys[i].Value.ToString();
// grvTestTable.Rows[i].Attributes.Add("onclick", "window.open(''Detail.aspx?PKID="+ID+" '')");
//}
#endregion
}
private void BindGridViewData()
{
string strCondition = "";
MSCL.PageHelper wp = new MSCL.PageHelper();
wp.TableName = "AFM_EmailMessage";
wp.KeyField = "EID";
wp.SortName = "EID";
wp.Condition = strCondition;
wp.CurrentPageIndex = AspNetPager1.CurrentPageIndex;
wp.PageSize = AspNetPager1.PageSize;//=PageSize;
DataTable dt = wp.GetDataTableMyPage();
AspNetPager1.RecordCount = wp.RecordCount;
grvAFM_EmailMessage.DataSource = dt;
grvAFM_EmailMessage.DataBind();
AspNetPager1.CustomInfoHTML = " 共<font color=''#FF8000''><b>" + wp.RecordCount.ToString() + "</b></font>条记录/";
AspNetPager1.CustomInfoHTML += " <font color=#FF8000''><b>" + wp.PageCount.ToString() + "</b></font>页";
AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>页";
/*
MSCL.PageHelper p = new PageHelper();
p.CurrentPageIndex = AspNetPager1.CurrentPageIndex;
p.FieldsName = "*";
p.KeyField = "d_id";
p.SortName = "d_id asc";
p.TableName = "testtable";
p.EndCondition = "count(*)";
p.PageSize = AspNetPager1.PageSize;
DataTable dt = p.QueryPagination();
AspNetPager1.RecordCount = p.RecordCount;
Repeater1.DataSource = dt;
Repeater1.DataBind();
AspNetPager1.CustomInfoHTML = " 共<b>" + p.RecordCount + "</b>条记录/";
AspNetPager1.CustomInfoHTML += "<b>" + p.PageCount + "</b>页";
AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + p.CurrentPageIndex + "</b></font>页";
*/
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
BindGridViewData();
}CREATE PROC P_viewPage
/*
敬告：适用于单一主键或存在唯一值列的表或视图
*/
@TableName VARCHAR(200), --表名
@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*
@PrimaryKey VARCHAR(100), --单一主键或唯一值键
@Where VARCHAR(2000), --查询条件 不含''where''字符，如id>10 and len(userid)>9
@Order VARCHAR(1000), --排序 不含''order by''字符，如id asc,userid desc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
@RecorderCount INT, --记录总数 0:会返回总记录
@PageSize INT, --每页输出的记录数
@PageIndex INT, --当前页数
@TotalCount INT OUTPUT, --记返回总记录
@TotalPageCount INT OUTPUT --返回总页数
AS
SET NOCOUNT ON
IF ISNULL(@TotalCount,'''') = '''' SET @TotalCount = 0
SET @Order = RTRIM(LTRIM(@Order))
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),'' '','''')
WHILE CHARINDEX('', '',@Order) > 0 OR CHARINDEX('' ,'',@Order) > 0
BEGIN
SET @Order = REPLACE(@Order,'', '','','')
SET @Order = REPLACE(@Order,'' ,'','','')
END
IF ISNULL(@TableName,'''') = '''' OR ISNULL(@FieldList,'''') = ''''
OR ISNULL(@PrimaryKey,'''') = ''''
OR @SortType < 1 OR @SortType >3
OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN
PRINT(''ERR_00'')
RETURN
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order,4))!='' ASC'' AND UPPER(RIGHT(@Order,5))!='' DESC'')
BEGIN PRINT(''ERR_02'') RETURN END
END
DECLARE @new_where1 VARCHAR(1000)
DECLARE @new_where2 VARCHAR(1000)
DECLARE @new_order1 VARCHAR(1000)
DECLARE @new_order2 VARCHAR(1000)
DECLARE @new_order3 VARCHAR(1000)
DECLARE @Sql VARCHAR(8000)
DECLARE @SqlCount NVARCHAR(4000)
IF ISNULL(@where,'''') = ''''
BEGIN
SET @new_where1 = '' ''
SET @new_where2 = '' WHERE ''
END
ELSE
BEGIN
SET @new_where1 = '' WHERE '' + @where
SET @new_where2 = '' WHERE '' + @where + '' AND ''
END
IF ISNULL(@order,'''') = '''' OR @SortType = 1 OR @SortType = 2
BEGIN
IF @SortType = 1
BEGIN
SET @new_order1 = '' ORDER BY '' + @PrimaryKey + '' ASC''
SET @new_order2 = '' ORDER BY '' + @PrimaryKey + '' DESC''
END
IF @SortType = 2
BEGIN
SET @new_order1 = '' ORDER BY '' + @PrimaryKey + '' DESC''
SET @new_order2 = '' ORDER BY '' + @PrimaryKey + '' ASC''
END
END
ELSE
BEGIN
SET @new_order1 = '' ORDER BY '' + @Order
END
IF @SortType = 3 AND CHARINDEX('',''+@PrimaryKey+'' '','',''+@Order)>0
BEGIN
SET @new_order1 = '' ORDER BY '' + @Order
SET @new_order2 = @Order + '',''
SET @new_order2 = REPLACE(REPLACE(@new_order2,''ASC,'',''{ASC},''),''DESC,'',''{DESC},'')
SET @new_order2 = REPLACE(REPLACE(@new_order2,''{ASC},'',''DESC,''),''{DESC},'',''ASC,'')
SET @new_order2 = '' ORDER BY '' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF @FieldList <> ''*''
BEGIN
SET @new_order3 = REPLACE(REPLACE(@Order + '','',''ASC,'','',''),''DESC,'','','')
SET @FieldList = '','' + @FieldList
WHILE CHARINDEX('','',@new_order3)>0
BEGIN
IF CHARINDEX(SUBSTRING('',''+@new_order3,1,CHARINDEX('','',@new_order3)),'',''+@FieldList+'','')>0
BEGIN
SET @FieldList =
@FieldList + '','' + SUBSTRING(@new_order3,1,CHARINDEX('','',@new_order3))
END
SET @new_order3 =
SUBSTRING(@new_order3,CHARINDEX('','',@new_order3)+1,LEN(@new_order3))
END
SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
END
END
SET @SqlCount = ''SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/''
+ CAST(@PageSize AS VARCHAR)+'') FROM '' + @TableName + @new_where1
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N''@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT'',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
ELSE
BEGIN
SELECT @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一页数据
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ''
+ @TableName + @new_where1 + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM (''
+ ''SELECT TOP '' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ '' '' + @FieldList + '' FROM ''
+ @TableName + @new_where1 + @new_order2 + '' ) AS TMP ''
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ''
+ @TableName + @new_where2 + @PrimaryKey + '' > ''
+ ''(SELECT MAX('' + @PrimaryKey + '') FROM (SELECT TOP ''
+ STR(@PageSize*(@PageIndex-1)) + '' '' + @PrimaryKey
+ '' FROM '' + @TableName
+ @new_where1 + @new_order1 +'' ) AS TMP) ''+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM (''
+ ''SELECT TOP '' + STR(@PageSize) + '' ''
+ @FieldList + '' FROM ''
+ @TableName + @new_where2 + @PrimaryKey + '' < ''
+ ''(SELECT MIN('' + @PrimaryKey + '') FROM (SELECT TOP ''
+ STR(@TotalCount-@PageSize*@PageIndex) + '' '' + @PrimaryKey
+ '' FROM '' + @TableName
+ @new_where1 + @new_order2 +'' ) AS TMP) ''+ @new_order2
+ '' ) AS TMP '' + @new_order1
END
END
IF @SortType = 2 --仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ''
+ @TableName + @new_where2 + @PrimaryKey + '' < ''
+ ''(SELECT MIN('' + @PrimaryKey + '') FROM (SELECT TOP ''
+ STR(@PageSize*(@PageIndex-1)) + '' '' + @PrimaryKey
+'' FROM ''+ @TableName
+ @new_where1 + @new_order1 + '') AS TMP) ''+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM (''
+ ''SELECT TOP '' + STR(@PageSize) + '' ''
+ @FieldList + '' FROM ''
+ @TableName + @new_where2 + @PrimaryKey + '' > ''
+ ''(SELECT MAX('' + @PrimaryKey + '') FROM (SELECT TOP ''
+ STR(@TotalCount-@PageSize*@PageIndex) + '' '' + @PrimaryKey
+ '' FROM '' + @TableName
+ @new_where1 + @new_order2 +'' ) AS TMP) ''+ @new_order2
+ '' ) AS TMP '' + @new_order1
END
END
IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IF CHARINDEX('','' + @PrimaryKey + '' '','','' + @Order) = 0
BEGIN PRINT(''ERR_02'') RETURN END
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ( ''
+ ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ( ''
+ '' SELECT TOP '' + STR(@PageSize*@PageIndex) + '' '' + @FieldList
+ '' FROM '' + @TableName + @new_where1 + @new_order1 + '' ) AS TMP ''
+ @new_order2 + '' ) AS TMP '' + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ( ''
+ ''SELECT TOP '' + STR(@PageSize) + '' '' + @FieldList + '' FROM ( ''
+ '' SELECT TOP '' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + '' '' + @FieldList
+ '' FROM '' + @TableName + @new_where1 + @new_order2 + '' ) AS TMP ''
+ @new_order1 + '' ) AS TMP '' + @new_order1
END
END
END
PRINT(@Sql)
EXEC(@Sql)
GO--利用ROW_NUMBER快速分页
ALTER PROCEDURE [dbo].[pr_Pagination]
@TableName varchar(500), --要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id)
@Fields varchar(500), --表中的字段，可以使用*代替
@OrderField varchar(500), --要排序的字段
@SqlWhere varchar(max), --WHERE子句
@PageSize int, --分页的大小
@PageIndex int, --要显示的页的索引
@TotalRecord int output,--记录总数
@TotalPage int output, --页的总数
@WholeContion varchar (1000) --存储过程中的条件
as
begin
--Begin Tran
Declare @sql nvarchar(4000);
Declare @Record int; --记录总数
--利用WHERE子句进行过滤
if (isnumeric(@WholeContion)=1 )
begin
set @sql = ''select @Record = count(*) from '' + @TableName + '' where 1=1 '' + @SqlWhere
EXEC sp_executesql @sql,N''@Record int OUTPUT'',@Record OUTPUT
if (CAST(@WholeContion as int ) < @Record )
begin
set @Record = CAST(@WholeContion as int )
end
end
else
begin
set @sql = ''select @Record = '' + @WholeContion + '' from '' + @TableName + '' where 1=1 '' + @SqlWhere
--执行sql语句得到记录总数
EXEC sp_executesql @sql,N''@Record int OUTPUT'',@Record OUTPUT
end
select @TotalPage=CEILING((@Record+0.0)/@PageSize)
select @TotalRecord=@Record
--select @TotalRecord
--select @TotalPage
--根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序
set @sql = ''select * from (select ROW_NUMBER() over(order by '' + @OrderField + '') as rowId,'' + @Fields + '' from '' + @TableName + '' where 1=1 '' + @SqlWhere
--确保当前页的索引在合理的范围之内
if @PageIndex<=0
Set @PageIndex = 1
--得到当前页在整个结果集中准确的ROW_NUMBER值
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@PageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
--输出当前页中的数据
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
--End
--Else
--Begin
--Commit Tran
--End
end
sql server 2005分页存储过程和sql server 2000分页存储过程
sql server 2005分页存储过程和sql server 2000分页存储过程，sql 2005的分页存储过程分3个版本，一个是没有优化过的，一个是优化过的，最后一个支持jion的，sql2000的分页存储过程，也可以运行在sql2005上，但是性能没有sql2005的版本好。
USE [svnhost]
GO
/****** 对象: StoredProcedure [dbo].[up_Page2005] 脚本日期: 05/21/2008 11:27:05 ******/
SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
CREATE proc [dbo].[up_Page2005]
@TableName varchar(50), --表名
@Fields
今天关于SqlServer 分页存储过程和sqlserver存储过程分页查询的分享就到这里，希望大家有所收获，若想了解更多关于mmssqlserver中三种分页存储过程代码、MS SqlServer 海量数据分页存储过程收集、MS SqlServer海量数据分页存储过程收集、sql server 2005分页存储过程和sql server 2000分页存储过程等相关知识，可以在本站进行查询。
本文标签：
