数据库技术:SQLServer分页查询通用存储过程(只做分页查询用)

  /*----------------------------------------------  *procedure name : p_pageresult  * author :fay  * create date : 2014-07-18  */  create procedure prcpageresult  -- 获得某一页的数据 --  @currpage int = 1, --当前页页码 (即top currpage)  @showcolumn varchar(2000) = '*', --需要得到的字段 (即 column1,column2,......)  @tabname varchar(2000), --需要查看的表名 (即 from table_name)  @strcondition varchar(2000) = '', --查询条件 (即 where condition......) 不用加where关键字  @asccolumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)  @bitordertype bit = 0, --排序的类型 (0为升序,1为降序)  @pkcolumn varchar(50) = '', --主键名称  @pagesize int = 20 --分页大小    as  begin -- 存储过程开始  -- 该存储过程需要用到的几个变量 --  declare @strtemp varchar(1000)  declare @strsql varchar(4000) --该存储过程最后执行的语句  declare @strordertype varchar(1000) --排序类型语句 (order by column asc或者order by column desc)    begin  if @bitordertype = 1 -- bitordertype=1即执行降序  begin  set @strordertype = ' order by '+@asccolumn+' desc'  set @strtemp = '<(select min'  end  else  begin  set @strordertype = ' order by '+@asccolumn+' asc'  set @strtemp = '>(select max'  end    if @currpage = 1 -- 如果是第一页  begin  if @strcondition != ''  set @strsql = 'select top '+str(@pagesize)+' '+@showcolumn+' from '+@tabname+  ' where '+@strcondition+@strordertype  else  set @strsql = 'select top '+str(@pagesize)+' '+@showcolumn+' from '+@tabname+@strordertype  end    else -- 其他页  begin  if @strcondition !=''  set @strsql = 'select top '+str(@pagesize)+' '+@showcolumn+' from '+@tabname+  ' where '+@strcondition+' and '+@pkcolumn+@strtemp+'('+@pkcolumn+')'+' from (select top '+str((@currpage-1)*@pagesize)+  ' '+@pkcolumn+' from '+@tabname+@strordertype+') as tabtemp)'+@strordertype  else  set @strsql = 'select top '+str(@pagesize)+' '+@showcolumn+' from '+@tabname+  ' where '+@pkcolumn+@strtemp+'('+@pkcolumn+')'+' from (select top '+str((@currpage-1)*@pagesize)+' '+@pkcolumn+  ' from '+@tabname+@strordertype+') as tabtemp)'+@strordertype  end    end  exec (@strsql)  end -- 存储过程结束  ------------------------------------------------  go

  /*----------------------------------------------  *procedure name : prcrowscount  * author :fay  * create date : 2014-07-18  */  create proc prcrowscount  @tabname varchar(200), --需要查询的表名  @colname varchar(200)='*', --需要查询的列名  @condition varchar(200)='' --查询条件  as  begin  declare @strsql varchar(255)  if @cOndition= ''  set @strsql='select count('+@colname+') from '+@tabname  else  set @strsql='select count('+@colname+') from '+@tabname+' where '+@condition  exec (@strsql)  end  ------------------------------------------------  go