分页存储过程

CREATE PROCEDURE [dbo].[SP_Pagination]  
/*  
***************************************************************  
** 分页存储过程 该存储过程适用于MSSQL2005 及之后版本**  
***************************************************************  
参数说明:  
1.Tables :表名称,视图  
2.PrimaryKey :主关键字  
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc  
4.CurrentPage :当前页码  
5.PageSize :分页尺寸  
6.Filter :过滤语句，不带Where   
7.Group :Group语句,不带Group By  
效果演示：http://www.cn5135.com/_App/Enterprise/QueryResult.aspx  
***************************************************************/  
(  
@Tables varchar(1000),  
@PrimaryKey varchar(100),  
@CurrentPage int,  
@PageSize int,  
@Filter varchar(1000),  
@Fields varchar(1000) = '*',  
@Sort varchar(200) = NULL,  
@Group varchar(1000) = NULL  
)  
AS  
	/*默认排序*/  
	IF @Sort IS NULL or @Sort = ''  
	SET @Sort = @PrimaryKey
	---------------------------
	 /*默认当前页*/  
	IF @CurrentPage < 1  
	SET @CurrentPage = 1  
	-------------------------------
	/*设置分页参数.*/ 
	DECLARE @strPageSize varchar(50)  
    DECLARE @strStartRow varchar(50)
    DECLARE @strEndRow varchar(50)
	SET @strPageSize = CAST(@PageSize AS varchar(50))  
	SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize) AS varchar(50))
	SET @strEndRow=CAST((@CurrentPage*@PageSize+1) AS varchar(50))
	IF @Sort IS NULL or @Sort = '' 
	Begin 
		SET @Sort = @PrimaryKey
	End 
	Declare @strSql nvarchar(1000)
	Set @strSql ='
    With TempTB AS
    (Select ROW_NUMBER() over(order by '+ @Sort+') As RowNum,'+ @Fields + ' from '+ @Tables +' where '+ @Filter +')
    Select * from TempTB where RowNum>'+ @strStartRow+ ' And RowNum<' + @strEndRow + '
    Select Count(1) from '+ @Tables +' where '+ @Filter 
    Exec (@strSql)