sql server 2005高效分页存储过程 ，这是在网上找到的经修改调试使用暂时没发现错误，发布在此供需要的人参考。开始那位大侠写出来可能是没有测试传条件的错误，现已经修改并加入了部分代码修正此错误。 由于有人提到如何使用此代码，我是用c#语言，把把引用
    sql server 2005高效分页存储过程 ，这是在网上找到的经修改调试使用暂时没发现错误，发布在此供需要的人参考。开始那位大侠写出来可能是没有测试传条件的错误，现已经修改并加入了部分代码修正此错误。由于有人提到如何使用此代码，我是用c#语言，把把引用代码贴出来共参考吧。 sqlite sqlserver jdbc driver if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[proc_datapaginglist]') and objectproperty(id, n'isprocedure') = 1)drop procedure [dbo].[proc_datapaginglist]gocreate procedure [dbo].[proc_datapaginglist](@tablename nvarchar(200), ----要显示的表或多个表的连接@fieldnames nvarchar(200)='*', ----要显示的字段列表@pagesize int = 10, ----每页显示的记录个数@page int = 10, ----要显示那一页的记录@pagecount int = 1 output, ----查询结果分页后的总页数@counts int = 1 output, ----查询到的总记录数@fieldsort nvarchar(200)= null,----排序字段列表或条件@sort bit = 1, ----排序方法，0为升序，1为降序--程序传参如：' sorta asc,sortb desc,sortc ')@condition nvarchar(200)= null,----查询条件,不需where@keyid nvarchar(100), ----主表的主键@distinct bit = 0 ----是否添加查询字段的 distinct 默认0不添加/1添加)asset nocount ondeclare @select nvarchar(1000) ----存放动态生成的sql语句declare @strcounts nvarchar(1000) ----存放取得查询结果总数的查询语句declare @strid nvarchar(1000) ----存放取得查询开头或结尾id的查询语句declare @sorttypea nvarchar(10) ----数据排序规则adeclare @sorttypeb nvarchar(10) ----数据排序规则bdeclare @distselect nvarchar(50) ----对含有distinct的查询进行sql构造declare @distcounts nvarchar(50) ----对含有distinct的总数查询进行sql构造declare @sortfielda nvarchar(50) ----对含有是否还有排序字段时的排序方式组合adeclare @sortfieldb nvarchar(50) ----对含有是否还有排序字段时的排序方式组合bif @distinct = 0 begin set @distselect = 'select ' set @distcounts = ' count(*)' endelse begin set @distselect = 'select distinct ' set @distcounts = ' count(distinct '+@keyid+')' endif @sort=0 begin set @sorttypeb=' asc ' set @sorttypea=' desc ' endelse begin set @sorttypeb=' desc ' set @sorttypea=' asc ' endif @fieldsort is not null and @fieldsort'' --排序字段不为空时 begin set @sortfieldb=' order by '+ @fieldsort +' '+ @sorttypeb set @sortfielda=' order by '+ @fieldsort +' '+ @sorttypea endelse begin set @sortfieldb='' set @sortfielda='' end--------生成查询语句----------此处@strcounts为取得查询结果数量的语句if @condition is null or @condition='' --没有设置显示条件 begin set @select = @fieldnames + ' from ' + @tablename set @strcounts = @distselect+' @counts='+@distcounts+' from '+@tablename set @strid = ' from ' + @tablename endelse begin set @select = + @fieldnames + 'from ' + @tablename + ' where ' + @condition set @strcounts = @distselect+' @counts='+@distcounts+' from '+@tablename + ' where ' + @condition set @strid = ' from ' + @tablename + ' where ' + @condition end----取得查询结果总数量-----exec sp_executesql @strcounts,n'@counts int out ',@counts outdeclare @tmpcounts intif @counts = 0 set @tmpcounts = 1else 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 @condition is null or @condition='' --没有设置显示条件 begin if @pageindex<2 or @page<=@pageindex / 2 + @pageindex % 2 --前半部分数据处理 begin set @strcounts=@distselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fieldnames+' from '+@tablename + ' where '+@keyid+' not in('+ @distselect+' top '+ cast(@pagesize*(@page-1) as varchar(20)) +' '+ @keyid +' from '+@tablename + @sortfieldb +')' + @sortfieldb end else begin set @page = @pageindex-@page+1 --后半部分数据处理 if @page <= 1 --最后一页数据显示 set @strcounts=@distselect+' * from ('+@distselect+' top '+ cast(@lastcount as varchar(4))+' '+ @fieldnames+' from '+@tablename + @sortfielda+') as temptb '+@sortfieldb else set @strcounts=@distselect+' * from ('+@distselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fieldnames+' from '+@tablename + ' where '+@keyid+' not in('+ @distselect+' top '+ cast(@pagesize*(@page-2)+@lastcount as varchar(20)) +' '+ @keyid +' from '+@tablename + @sortfielda+')' + @sortfielda+') as temptb '+@sortfieldb end end else --有查询条件 begin if @pageindex<2 or @page<=@pageindex / 2 + @pageindex % 2 --前半部分数据处理 begin set @strcounts=@distselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fieldnames +' from '+@tablename + ' where '+@keyid+' not in('+ @distselect+' top '+ cast(@pagesize*(@page-1) as varchar(20)) +' '+ @keyid +' from '+@tablename + ' where ' + @condition + @sortfieldb+')'+' and ' + @condition + @sortfieldb end else begin set @page = @pageindex-@page+1 --后半部分数据处理 if @page <= 1 --最后一页数据显示 set @strcounts=@distselect+' * from ('+@distselect+' top '+ cast(@lastcount as varchar(4))+' '+ @fieldnames+' from '+@tablename + ' where '+ @condition +@sortfielda+') as temptb '+@sortfieldb else set @strcounts=@distselect+' * from ('+@distselect+' top '+ cast(@pagesize as varchar(4))+' '+ @fieldnames+' from '+@tablename + ' where '+@keyid+' not in('+ @distselect+' top '+ cast(@pagesize*(@page-2)+@lastcount as varchar(20)) +' ' + @keyid +' from '+@tablename +' where '+ @condition +@sortfielda+')' +' and '+ @condition +@sortfielda+') as temptb ' + @sortfieldb end end------返回查询结果-----exec sp_executesql @strcountsset nocount off
//调用以下必须先创建一个datapagelist 对象，用引用型传递这个对象来访问以下getdatapagelist函数，并返回datapagelist 对象。这个返回的对象中包括很多属性，直接引用需要的属性即可，见属性类介绍。 private static readonly string mconnectionstring = configurationmanager.connectionstrings[fleamarketconnstring].connectionstring; /// /// /// /// /// public datapagelist getdatapagelist(ref datapagelist dpl) { sqlconnection conn = new sqlconnection(mconnectionstring); sqldataadapter da = new sqldataadapter(); da.selectcommand = new sqlcommand(); da.selectcommand.connection = conn; da.selectcommand.commandtext = proc_datapaginglist; da.selectcommand.commandtype = commandtype.storedprocedure; da.selectcommand.parameters.add(@tablename, sqldbtype.nvarchar, 200).value = dpl.tablename; da.selectcommand.parameters.add(@fieldnames, sqldbtype.nvarchar, 200).value = dpl.fieldnames; da.selectcommand.parameters.add(@pagesize, sqldbtype.int).value = dpl.pagesize; da.selectcommand.parameters.add(@page, sqldbtype.int).value = dpl.page; da.selectcommand.parameters.add(@pagecount, sqldbtype.int).direction = parameterdirection.output; da.selectcommand.parameters.add(@counts, sqldbtype.int).direction = parameterdirection.output; da.selectcommand.parameters.add(@fieldsort, sqldbtype.nvarchar, 100).value = dpl.fieldsort; da.selectcommand.parameters.add(@sort, sqldbtype.bit).value = dpl.sort; da.selectcommand.parameters.add(@condition, sqldbtype.nvarchar, 200).value = dpl.condition; da.selectcommand.parameters.add(@keyid, sqldbtype.nvarchar, 100).value = dpl.keyid; da.selectcommand.parameters.add(@distinct, sqldbtype.bit).value = dpl.distinct; dataset ds = new dataset(); da.fill(ds); //pagelistreturnvalue plr = new pagelistreturnvalue(); dpl.getdataset = ds; dpl.counts = int.parse(da.selectcommand.parameters[@counts].value.tostring()); dpl.pagecount = int.parse(da.selectcommand.parameters[@pagecount].value.tostring()); //object[] obj = new object[3]; //obj[0] = ds; //obj[1] = da.selectcommand.parameters[@counts].value; //obj[2] = da.selectcommand.parameters[@pagecount].value; return dpl; }//以下是关于数据参数属性类。/// /// 高效分页输出输入参数属性 /// public class datapagelist { private int _page=1;//第多少页 private int _pagesize = 10;//请求页面大小 private string _jsonrowsname = rows; private string _tablename = ; private string _fieldnames = *;//默认为所有数据 private string _fieldsort = ; private byte _sort = 1; private string _condition = ; private string _keyid = ; private byte _distinct = 0; private int _counts = 10;//总页数 private int _pagecount = 10;//总的多少页面 private dataset _dataset = null; /// /// 总记录数 /// public int counts { get { return _counts; } set { _counts = value; } } /// /// 总分页数 /// public int pagecount { get { return _pagecount; } set { _pagecount = value; } } /// /// 数据集 /// public dataset getdataset { get { return _dataset; } set { _dataset = value; } } /// /// 第几页 /// public int page { get { return _page; } set { _page = value; } } /// /// 每页显示的记录数 /// public int pagesize { get { return _pagesize; } set { _pagesize = value; } } /// /// flexigrid json行名，固定为rows /// public string jsonrowsname { get { return _jsonrowsname; } //set { _jsonrowsname = value; } } /// /// 表名或视图名称 /// public string tablename { get { return _tablename; } set { _tablename = value; } } /// /// 字段名，默认为* /// public string fieldnames { get { return _fieldnames; } set { _fieldnames = value; } } /// /// 排序字段 /// public string fieldsort { get { return _fieldsort; } set { _fieldsort = value; } } /// /// 排序方式，默认为1，表示降序；0表示升序 /// public byte sort { get { return _sort; } set { _sort = value; } } /// /// 查询条件，不加where /// public string condition { get { return _condition; } set { _condition = value; } } /// /// 表主键 /// public string keyid { get { return _keyid; } set { _keyid = value; } } /// /// 是否消除记录重复，同时包括计数,默认0表示不添加 /// public byte distinct { get { return _distinct; } set { _distinct = value; } } }