通用分頁存儲過程.

MSSQL中要想分頁只能借助 row_number() over( order by'') 函數以下是我修改后的一個通用存儲過程,

CREATE PROCEDURE [dbo].[Common_Method_Page] 
	@columns varchar(max)=N'*', --要显示的列名，用逗号隔开 
	@tableName varchar(max), --要查询的表名 
	@tempTableName varchar(50)=N'tempTable',--表名的簡寫 
	@orderColumnName varchar(max), --排序的列名 
	@order varchar(max) ='ASC', --排序的方式，升序为asc,降序为 desc 
	@where varchar(max) = '1=1', --where 条件，如果不带查询条件，请用 1=1 
	@pageIndex int =1, --当前页索引 
	@pageSize int =20, --页大小(每页显示的记录条数)
    @remarkColumn varchar(max)='', --當多表查詢時,用於查詢副表的字段
	@pageCount int=null output, --总页数，输出参数 
	@records int=null output --總記錄數
as 
begin 

-- author:MichaelYang
-- modifiedDate  :2011-9-7 
--這是一個動態分頁的存儲過程
	declare @sqlRecordCount nvarchar(max) --得到总记录条数的语句 
	declare @sqlSelect nvarchar(max) --查询语句 
--print ''[email protected] +'';
set @columns = ISNULL(@columns,N'*'); --默認查詢出所有的記錄
set @order=ISNULL(@order,N'ASC');     --默認按升序排序
set @pageIndex = ISNULL(@pageIndex,1);--默認從第一頁開始
set @pageSize =ISNULL(@pageSize,20);  --設置頁面大小為20條記錄
set @where = ISNULL(@where,N'1=1');   --默認按條件查詢為真
set @tempTableName =ISNULL(@tempTableName,N'tempTable');
set 
	@sqlRecordCount=N'select @recordCount=count(*) from ' [email protected] + ' where '+ @where 
	print '[@sqlRecordCount:]'[email protected]
declare @recordCount int --保存总记录条数的变量 
exec sp_executesql @sqlRecordCount,N'@recordCount int output',@recordCount output ;
--动态 sql 传参 
if( @recordCount % @pageSize = 0) --如果总记录条数可以被页大小整除 
	   set @pageCount = @recordCount / @pageSize --总页数就等于总记录条数除以页大小 
else --如果总记录条数不能被页大小整除 
	   set @pageCount = @recordCount / @pageSize + 1 --总页数就等于总记录条数除以页大小加1 
if(@remarkColumn is null) or (@remarkColumn ='') or (@remarkColumn ='null')
	   set @sqlSelect = N'select '[email protected]+' from (select row_number() over (order by ' [email protected]+' '[email protected] +') as tempid,* from ' [email protected]+' where '+ @where +') as '[email protected]+' where tempid between ' +str((@pageIndex - 1)*@pageSize + 1 ) +' and '+str( @pageIndex * @pageSize) 
else
       set  @sqlSelect = N'select '[email protected]+','[email protected]+' from (select row_number() over (order by ' [email protected]+' '[email protected] +') as tempid,*,'[email protected]+' from ' [email protected]+' where '+ @where +') as '[email protected]+' where tempid between ' +str((@pageIndex - 1)*@pageSize + 1 ) +' and '+str( @pageIndex * @pageSize) 
print '@sqlSelect:'[email protected];
exec (@sqlSelect)--执行动态Sql 
set @records [email protected];
end