100 求分求分页存储过程及C#调用示例
分求分页存储过程及C#调用示例 最好可以详细一点的,分页存储过程可以和gridview绑定吗? 谢谢

[解决办法]
分页方法很多,我用的是增加临时表的方式.以NorthWind为例:
CREATE PROCEDURE dbo.GetCustomerDataList
(
@pageCount int,
@lineCount int
)
AS
SET NOCOUNT ON

DECLARE @initCount int

SET @initCount = @lineCount * (@pageCount - 1)

if(@pageCount = 1 and @initCount = 0)
set @initCount = 1

SET ROWCOUNT @initCount

SELECT
CustomerID
INTO
#WORK
FROM
Customers
ORDER BY
CustomerID

SET ROWCOUNT @lineCount
SELECT
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
FROM
Customers
WHERE
(@pageCount <> 1
ANDNOT EXISTS(
SELECT * FROM #WORK
WHERE
#WORK.CustomerID = Customers.CustomerID
)
)

OR
(@pageCount = 1 AND 1 = 1)

ORDER BY
CustomerID

DROP TABLE #WORK

RETURN


GO

[解决办法]
给你个简单有效的分页存储过程吧：
ALTER PROCEDURE dbo.GetData
@Size INT = 50, --每页记录数
@Index INT = 1, --页索引
@SQL NVARCHAR(4000), --任何查询语句
@RowCount int = 0 OUTPUT -- 查询结果的总记录数
AS
DECLARE @i INT

SET　NOCOUNT ON

SET @Index=(@Index-1)*@Size+1
EXEC SP_CURSOROPEN @i OUTPUT,@SQL ,@scrollopt=1,@ccopt=1, @rowcount= @Count OUTPUT
EXEC SP_CURSORFETCH @i,16,@Index,@Size
EXEC SP_CURSORCLOSE @i

SET NOCOUNT OFF
[解决办法]
分页存储过程

http://blog.csdn.net/hertcloud/archive/2006/04.aspx
[解决办法]
create proc sp_PublicTurnPage(
@TBName nvarchar(2000)= ' ',--表名,如 pinyin
@PageSizeint=10,--每页的记录数,默认为 10
@CurPageint=1,--表示当前页 1
@KeyFieldnvarchar(100)= 'ID ',--关键字段名，默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDescnvarchar(4)= 'ASC ',--关键字的升、降序，默认为升序 ASC , 降序为 DESC
@Fieldsnvarchar(2000)= '* ',--所选择的列名，默认为全选
@Conditionnvarchar(2000)= ' ',--where 条件,默认为空
@Ordernvarchar(200)= ' '--排序条件,默认为空
)as
if @TBName = ' '
begin
raiserror( '请指定表名！ ',11,1)
return
end
if @PageSize <=0 or @CurPage <0
begin
raiserror( '当前页数和每页的记录数都必须大于零！ ',11,1)
return
end
if @KeyAscDesc = 'DESC '
set @KeyAscDesc = ' < '
else
set @KeyAscDesc = '> '
if @Condition <> ' '
set @Condition = ' where ' + @Condition
declare @SQL nvarchar(2000)
set @SQL = ' '
if @CurPage = 1
set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TBName + @Condition + ' ' + @Order
else
begin
declare @iTopNum int
set @iTopNum = @PageSize * (@CurPage - 1)
set @SQL = @SQL + 'declare @sLastValue nvarchar(100) ' + char(13)
set @SQL = @SQL + 'SELECT Top ' + cast(@iTopNum as nvarchar(20)) + ' @sLastValue= ' + @KeyField + ' FROM ' + @TBName + @Condition + ' ' + @Order + char(13)

declare @Condition2 nvarchar(200)
if @Condition = ' '
set @Condition2 = ' where ' + @KeyField + @KeyAscDesc + '@sLastValue '
else
set @Condition2 = ' and ' + @KeyField + @KeyAscDesc + '@sLastValue '
set @SQL = @SQL + 'SELECT Top ' + cast(@PageSize as nvarchar(20)) + ' ' + @Fields + ' FROM ' + @TBName + @Condition + @Condition2 + @Order
end
EXECUTE sp_executesql @SQL
go
第二种：
CREATE PROCEDURE sp_PublicTurePage
@TBName nvarchar(100)= ' ',--表名,如 pinyin
@PageSizeint=10,--每页的记录数,默认为 10
@CurPageint=1,--表示当前页 1
@KeyFieldnvarchar(100)= 'ID ',--关键字段名，默认为 ID,该字段要求是表中的索引 或 无重复和不为空的字段
@KeyAscDescnvarchar(4)= 'ASC ',--关键字的升、降序，默认为升序 ASC , 降序为 DESC
@Fieldsnvarchar(500)= '* ',--所选择的列名，默认为全选
@Conditionnvarchar(200)= ' ',--where 条件,默认为空
@Ordernvarchar(200)= ' '--排序条件,默认为空
as
if @TBName = ' '
begin
raiserror( '请指定表名！ ',11,1)
return
end
if @PageSize <=0 or @CurPage <0
begin
raiserror( '当前页数和每页的记录数都必须大于零！ ',11,1)
return
end
if @Condition <> ' '
set @Condition = ' and ' + @Condition
DECLARE @Str nVARCHAR(4000)
set @Str= 'SELECT TOP '+convert(varchar(12),@PageSize)+ ' ' +@Fields+ ' from ' +@TBName+ ' where ' +@KeyField+ '
not in (select top ' + convert(varchar(12),(@PageSize*(@CurPage-1))) + ' ' +@KeyField+ ' from ' +@TBName+ '
order by ' +@KeyField+ ') ' + @Condition + 'order by ' + @KeyField + ' '

EXEC sp_ExecuteSql @Str
GO

[解决办法]
hbxtlhx(平民百姓) ( ) 信誉：112 Blog
－－－－－－－－－－－－－－－－－－－－－－
为正确解释
偶用过可以的
给你个简单有效的分页存储过程吧：
ALTER PROCEDURE dbo.GetData
@Size INT = 50, --每页记录数
@Index INT = 1, --页索引
@SQL NVARCHAR(4000), --任何查询语句
@RowCount int = 0 OUTPUT -- 查询结果的总记录数
AS
DECLARE @i INT

SET　NOCOUNT ON

SET @Index=(@Index-1)*@Size+1
EXEC SP_CURSOROPEN @i OUTPUT,@SQL ,@scrollopt=1,@ccopt=1, @rowcount= @Count OUTPUT
EXEC SP_CURSORFETCH @i,16,@Index,@Size
EXEC SP_CURSORCLOSE @i

SET NOCOUNT OFF

[解决办法]
C#
public static DataTable RunExecuteQuery(string strSQL, string order, int iCurrentPage, int iPageSize, out int count)
{
if (strSQL != " ")
{
IDatabase conn = null;
DataTable dtTmp = null;
try
{
conn = DatabaseFactory.CreateObject();
//要查询的表、视图或查询语句
System.Data.SqlClient.SqlParameter par1 = new System.Data.SqlClient.SqlParameter( "@Source ", strSQL);
par1.Direction = ParameterDirection.Input;
//order by
System.Data.SqlClient.SqlParameter par2 = new System.Data.SqlClient.SqlParameter( "@Order ", order);
par2.Direction = ParameterDirection.Input;
//返回列表
System.Data.SqlClient.SqlParameter par3 = new System.Data.SqlClient.SqlParameter( "@List ", "* ");
par3.Direction = ParameterDirection.Input;
//设置返回页码
System.Data.SqlClient.SqlParameter par4 = new System.Data.SqlClient.SqlParameter( "@Index ", iCurrentPage);

par4.Direction = ParameterDirection.Input;
//每页记录数
System.Data.SqlClient.SqlParameter par5 = new System.Data.SqlClient.SqlParameter( "@Size ", iPageSize);
par5.Direction = ParameterDirection.Input;
//查询到的记录总数
System.Data.SqlClient.SqlParameter par6 = new System.Data.SqlClient.SqlParameter( "@Count ", SqlDbType.Int);
par6.Direction = ParameterDirection.Output;

conn.Parameters.Add(par1);
conn.Parameters.Add(par2);
conn.Parameters.Add(par3);
conn.Parameters.Add(par4);
conn.Parameters.Add(par5);
conn.Parameters.Add(par6);

dtTmp = conn.ExecuteQueryStoreProcedure( "pager ");
//引用方式
count = int.Parse(par6.Value.ToString());
if (count > 0)
{
return dtTmp;
}
else
{
return null;
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
count = 0;
return null;
}
finally
{
if (conn != null)
{
conn.Close();
}
conn = null;
dtTmp = null;
}
}
else
{
count = 0;
return null;
}
}

SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER Proc [dbo].[pager]
@Source nvarchar(4000), --表名、视图名、查询语句
@Size int=10, --每页的大小(行数)
@Index int=1, --要显示的页
@List nvarchar (1000)= ' ', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@Order nvarchar (1000)= ' ', --排序字段列表
@Count int = null OUTPUT -- 输出记录数, 如果@Count为null, 则输出记录数, 否则不要输出
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件


select @Obj_ID=object_id(@Source)
,@List=case isnull(@List, ' ') when ' ' then ' * ' else ' '+@List end
,@Order=case isnull(@Order, ' ') when ' ' then ' ' else ' order by '+@Order end
,@Source=case when @Obj_ID is not null then ' '+@Source else ' ( '+@Source+ ') a ' end

--如果显示第一页，可以直接用top来完成
if @Index=1
begin
if @Count is null
begin
declare @lbtop1 nvarchar(1000)
set @lbtop1 = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbtop1, N '@Count int out ', @Count out
end
select @Id1=cast(@Size as varchar(20))
exec( 'select top '+@Id1+@List+ ' from '+@Source+@Order)
return
end

--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID, 'IsTable ')=1
begin
select @Id1=cast(@Size as varchar(20))
,@Id2=cast((@Index-1)*@Size as varchar(20))

select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键

begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype= 'PK ')
goto lbusetemp --如果表中无主键,则用临时表处理

select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype= 'PK ' and parent_obj=@Obj_ID
)))
if @@rowcount> 1 --检查表中的主键是否为复合主键
begin
select @strfd= ' ',@strjoin= ' ',@strwhere= ' '
select @strfd=@strfd+ ',[ '+name+ '] '
,@strjoin=@strjoin+ ' and a.[ '+name+ ']=b.[ '+name+ '] '
,@strwhere=@strwhere+ ' and b.[ '+name+ '] is null '
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype= 'PK ' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp

/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
if @Count is null
begin
declare @lbuseidentity nvarchar(1000)
set @lbuseidentity = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbuseidentity, N '@Count int out ', @Count out
end
exec( 'select top '+@Id1+@List+ ' from '+@Source
+ ' where '+@FdName+ ' not in(select top '
+@Id2+ ' '+@FdName+ ' from '+@Source+@Order
+ ') '+@Order
)
return

/*--表中有复合主键的处理方法--*/
lbusepk:
if @Count is null
begin
declare @lbusepk nvarchar(1000)
set @lbusepk = 'select @Count = count(*) from '+@Source
exec sp_executesql @lbusepk, N '@Count int out ', @Count out
end

exec( 'select '+@List+ ' from(select top '+@Id1+ ' a.* from
(select top 100 percent * from '+@Source+@Order+ ') a
left join (select top '+@Id2+ ' '+@strfd+ '
from '+@Source+@Order+ ') b on '+@strjoin+ '
where '+@strwhere+ ') a '
)
return

/*--用临时表处理的方法--*/
lbusetemp:
select @FdName= '[ID_ '+cast(newid() as varchar(40))+ '] '
,@Id1=cast(@Size*(@Index-1) as varchar(20))
,@Id2=cast(@Size*@Index-1 as varchar(20))

declare @lbusetemp nvarchar(4000)
set @lbusetemp = 'select '+@FdName+ '=identity(int,0,1), '+@List+ '
into #tb from '+@Source+@Order
+case when @Count is null then ' set @Count = @@rowcount ' else ' ' end + '
select '+@List+ ' from #tb where '+@FdName+ ' between '
+@Id1+ ' and '+@Id2
exec sp_executesql @lbusetemp, N '@Count int out ', @Count out


[解决办法]
我用了两个,一个计算页数,一个查询结果:
CREATE PROCEDURE docount
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@strWhere varchar(5000) = ' ', -- 查询条件 (注意: 加 where)
@RecordCount int output
AS
declare @strSQL nvarchar(4000)
set @strSQL = 'select @RecordCount=Count( '+@fldName+ ') from [ ' + @tblName + '] '+@strWhere
exec sp_executesql @strSQL,N '@RecordCount int output ',@RecordCount output
GO

CREATE procedure select_pagesize

(
@select_list varchar(2000),--不需要select
@table_name varchar(200),
@where varchar(2000),--不需要where
@primary_key varchar(200),--当是表联合时，加表名前缀.
@order_by varchar(400),--需要完整的子句 order by ...
@page_size smallint,--每页记录
@page_index int,--页索引
@do_count bit)--1只统计总数
as
/*
过程名：通用存储过程分页
使用示例:
单表sql调用：exec select_pagesize 'login_id,login_name ', 'tb_login ', ' login_name like ' '%% ' ' ', 'login_id ', ' order by login_dt desc ',20,10
多表sql调用：exec select_pagesize 'a.login_id,a.login_name,b.pro_name ', 'tb_login a,tb_code_province b ', ' a.pro_id=b.pro_id and a.login_name like ' '%% ' ' ', 'a.login_id ', ' order by a.login_dt desc ',20,10
备注：外部程序调用不需要转义单引号
原型结构：select top 20 select_list
from tablename
where z_id not in(select z_id from (select top 100 z_id from tablename order by order_by) temptable)
and ...
order by order_by

*/

declare @sql_str varchar(8000)
declare @record_min int
declare @new_where varchar(2000),@newin_where varchar(2000)
if @where= ' '--重新为梳理，此过程时性能的考虑，因此不使用 where 1=1 再追加条件。
begin
select @new_where= ' '
select @newin_where= ' '
end
else
begin
select @new_where= ' and '+@where
select @newin_where= ' where '+@where
end

if @do_count=1
select @sql_str= 'select count(*) from '+@table_name+@newin_where
else
if @page_index=1
if @where= ' '
select @sql_str= 'select top '+convert(varchar,@page_size)+ ' '+@select_list+ ' from '+@table_name+ ' '+@order_by
else
select @sql_str= 'select top '+convert(varchar,@page_size)+ ' '+@select_list+ ' from '+@table_name+ ' where '+@where+ ' '+@order_by
else
begin
select @record_min=(@page_index-1)*@page_size
select @sql_str= 'select top '+convert(varchar,@page_size)+ ' '+@select_list+ ' from '+@table_name+ ' where '+@primary_key+ ' not in (select '+stuff(@primary_key,1,charindex( '. ',@primary_key), ' ')
select @sql_str=@sql_str+ ' from (select top '+convert(varchar,@record_min)+ ' '+@primary_key+ ' from '+@table_name+@newin_where+ ' '+@order_by+ ') temptable0000) '
select @sql_str=@sql_str+@new_where+ ' '+@order_by
end
--print @sql_str
exec(@sql_str)
GO


[解决办法]
调用:

docount( "Files ", "Id ", "where Type = "+Compositor,15);
RangeValidator1.MaximumValue = LabelPage;
RangeValidator1.MinimumValue = "1 ";
binddata( "Files a ", "a.Id ", " a.Type = "+Compositor, "a.id,a.FileName,a.Up_Date,a.FileAuthor,a.AuthorGroup,a.Type ", "order by a.Up_Date desc,a.id desc ");


private void docount(string talName,string fldName,string strWhere,int Page_Size)
{
SqlCommand Command=new SqlCommand();
Command.Connection=Con;
Command.CommandText= "docount ";
Command.CommandType=CommandType.StoredProcedure;
Command.Parameters.Add( "@tblName ",talName);
Command.Parameters.Add( "@strWhere ",strWhere);//条件要加where
Command.Parameters.Add( "@fldName ",fldName);
SqlParameter workParm;
workParm = Command.Parameters.Add( "@RecordCount ", SqlDbType.Int);
workParm.Direction = ParameterDirection.Output;
Con.Open();

Command.ExecuteScalar();
Con.Close();
Int32 RecordCount = (Int32)Command.Parameters[ "@RecordCount "].Value;
PageSize = Page_Size;
if(RecordCount%PageSize> 0)
intPageCount = (RecordCount/PageSize)+1;
else
intPageCount = RecordCount/PageSize;
if(intPageCount > 0)
LabelPage = intPageCount.ToString();
else
LabelPage = "1 ";
LabelRecord = RecordCount.ToString();
//this.Response.Write(RecordCount.ToString());
if(RecordCount == 0)
{
str_Record = "暂无信息 !!! ";

}
}
private void binddata(string tblName,string fldName,string strWhere,string allfldName,string Str_Order)//条件要不加where
{
if (Request[ "CurrentPage "]==null)
{
intPageNo = 1;
}
else
{
intPageNo = Int32.Parse(Request[ "CurrentPage "]);
}
SqlCommand MyCommand=new SqlCommand();
MyCommand.Connection=Con;
MyCommand.CommandText= "select_pagesize ";
MyCommand.CommandType=CommandType.StoredProcedure;
MyCommand.Parameters.Add( "@table_name ",tblName);
MyCommand.Parameters.Add( "@primary_key ",fldName);
MyCommand.Parameters.Add( "@select_list ",allfldName);
MyCommand.Parameters.Add( "@page_size ",PageSize);
MyCommand.Parameters.Add( "@page_index ",intPageNo);
MyCommand.Parameters.Add( "@where ",strWhere);//条件要不加where
MyCommand.Parameters.Add( "@order_by ",Str_Order);
MyCommand.Parameters.Add( "@do_count ", "0 ");
Con.Open();
Repeater.DataSource = MyCommand.ExecuteReader();
Repeater.DataBind();
Con.Close();
if(LabelRecord != "0 ")
LabelRow = intPageNo.ToString();
else
LabelRow = "0 ";

if (intPageNo> 1)
{
HLFistPage.NavigateUrl = Request.CurrentExecutionFilePath+ "?Compositor= "+Compositor+ "&CurrentPage=1&Bg_Color= "+Bg_Color;
HLPrevPage.NavigateUrl = String.Concat(Request.CurrentExecutionFilePath+ "?Compositor= "+Compositor+ "&Bg_Color= "+Bg_Color+ "&CurrentPage= ", " ",intPageNo-1);
}
else
{
HLFistPage.NavigateUrl = " ";
HLPrevPage.NavigateUrl = " ";
}

if (intPageNo <intPageCount)
{
HLNextPage.NavigateUrl = String.Concat(Request.CurrentExecutionFilePath+ "?Compositor= "+Compositor+ "&Bg_Color= "+Bg_Color+ "&CurrentPage= ", " ",intPageNo+1);
HLEndPage.NavigateUrl = String.Concat(Request.CurrentExecutionFilePath+ "?Compositor= "+Compositor+ "&Bg_Color= "+Bg_Color+ "&CurrentPage= ", " ",intPageCount);
}
else
{
HLNextPage.NavigateUrl = " ";
HLEndPage.NavigateUrl = " ";
}
}
[解决办法]
Create procedure [dbo].[p_Page]
@sqlstr nvarchar(4000), --查询字符串
@start int, --第几个记录开始
@pagesize int --每页行数
as
declare @rowcount int
set nocount on
declare @P1 int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
exec sp_cursorfetch @P1,16,@start,@pagesize
select @rowcount
[解决办法]
详尽代码
http://www.javavsnet.com/bbs/View.aspx?id=503&boardid=16