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)
posted @ 2011-07-14 20:58  尐肥羊  阅读(196)  评论(0)  编辑  收藏  举报
up