sql2005 存儲過程實現分頁

-- =============================================
-- Author:        <尐肥羊>
-- Create date: <2010-07-13>
-- Description:    <利用row_number()存儲過程分頁>
-- =============================================
CREATE PROCEDURE [dbo].[proc_SplitPage]
@tblName varchar(255),
@strOrder varchar(255),
@strFields varchar(1000)='*',
@strOrderType varchar(10)='ASC',
@PageSize int =10,
@PageIndex int=1,
@strWhere varchar(1500)=''

AS

declare @strSql varchar(5000)

if @strWhere !=''
set @strWhere=' where '+@strWhere  --where要有空格， 還有from也要加空格
set @strSQL=
'SELECT * FROM (' +
    'SELECT ROW_NUMBER() OVER(ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strFields+' '+ 
     'FROM '+@tblName+' '+@strWhere+')  AS sp   
    WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND ' +str(@PageIndex*@PageSize)
print (@strSQL)
exec(@strSQL)