两种分页存储过程

可以用sql的跟踪器来验证程序对数据库的操作次数；
//多表储存过程(使用性更强，性能更强)
CREATE Procedure SP_Pagering
@tablename varchar(50),
@primarykey varchar(50),
@colunmnames varchar(1000),
@otherjoin varchar(1000),
@where varchar(1000),
@index int,
@size int
AS

declare @temp int

set @temp=@size*(@index-1)
exec('select top '+@size+' '+@colunmnames+' from '+@tablename+' '+@otherjoin +' where  '+@where +' '+@primarykey+' not in (select top '+@temp +' '+@primarykey+' from '+@tablename+' order by '+@primarykey+' )')

GO

//原始存储过程
create Procedure SP_Pager
@size int ,
@pageIndex int,
@count int,
@tablename varchar(50),
@primarykey varchar(50),
as
declare @tempcount int
declare @totalpage int

if @size<0
set @size=10
if @count%@size>0
begin
set @totalpage=@count/@size+1
end
else
begin
set @totalpage=@count/@size
end
if @pageindex<=0
set @pageindex=1
if @pageindex>@totalpage
begin
set @pageindex=@totalpage
end
set @tempcount=@size*(@pageindex-1)
if @pageindex<=@totalpage and @pageindex>=1
execute('select top '+@size+' * from '+@tablename+' where '+@primarykey+ not in (select top '+@tempcount+' '+@primarykey+' from '+@tablename+')'')
GO

//多表连接查询总数的存储过程
CREATE Procedure SP_TotalCount
@tablename varchar(50),
@primarykey varchar(50),
@otherjoin varchar(1000),
@where varchar(1000)
AS

exec('select count('+@primarykey+') from '+@tablename+' '+@otherjoin+' ' +@where )
GO

//DAL调用多表的两个存储过程
       private string sp_Pagering = "SP_Pagering";
       private string sp_TotalCount = "SP_TotalCount";

       public DataTable Pagering(string tableName,string primaryKey,string colunmNames,string otherJoin,string where,int index,int size)
       {

           if (where != String.Empty)
           {
               where = where + " and ";
           }
          return SqlHelper.ExecuteDataset(DataProvider.ConnectionString,sp_Pagering,tableName, primaryKey, colunmNames, otherJoin, where, index, size).Tables[0];
       
       }

       public int TotalCount(string tableName,string primaryKey,string otherJoin,string where)
       {
           if (where != String.Empty)
           {
               where =  " where "+where;
           }
           return (int)SqlHelper.ExecuteScalar(DataProvider.ConnectionString,sp_TotalCount, tableName, primaryKey, otherJoin, where);
       }