分页存储过程

----------分页存储过程，可用于SQL Server 2005/2008 ----------调用示例：Execute Pagination '字段列表','表名','条件','排序',10,1,0,'' Create Procedure [dbo].[PAGINATION] ( @fieldsList nvarchar(500), --字段列表，如：ID,TITLE @tableName nvarchar(128), --表名称 @where nvarchar(255), --SQ条件L语句，如：ID > 1986 @orderBy nvarchar(255), --SQL排序语句，如：ID Desc @pageSize int = 10, --每页记录条数 @absolutePage int = 1, --希望得到第几页 @recordCount int output, --总条数（输出参数） @sql nvarchar(4000) output --完整SQL语句（输出参数） ) As If (LEN(@fieldsList) < 1) --未填写@fieldsList则@fieldsList赋值为* Set @fieldsList = '*' If (LEN(@where) < 1) --未填写@where则@where赋值为1=1 Set @where = '1=1' If (LEN(@orderBy) < 1) --未填写@orderBy Begin --未填写@orderBy则@orderBy赋值为该表主键名称 Select @orderBy=COLUMN_NAME From INFORMATION_SCHEMA.KEY_COLUMN_USAGE Where TABLE_NAME = + @tableName If (LEN(@orderBy) < 1) --该表无主键，@orderBy赋值为该表标识列的名称 Select @orderBy=COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = + @tableName And ColumnProperty(Object_ID('V_VIDEO'),COLUMN_NAME,'IsIdentity')=1 If (LEN(@orderBy) < 1) --该表无标识列，@orderBy赋值为该表第一列的名称 Select Top 1 @orderBy=COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = + @tableName End Set @sql = 'Select @tempCount=Count(*) From ' + @tableName + ' Where ' + @where Execute sp_executesql @sql,N'@tempCount int output',@recordCount output --计算总条数 --Print @sql If (@absolutePage <> 1) --第3、4、5……页 Begin Declare @startRowNo int,@endRowNo int Set @endRowNo = @pageSize * @absolutePage --结束行 Set @startRowNo = @endRowNo - @pageSize + 1 --开始行，行号从1开始 Set @sql = 'Select ' + @fieldsList + ' From (Select ROW_NUMBER() OVER(Order By ' + @orderBy +') As RowNo,' + @fieldsList + ' From ' + @tableName + ' Where ' + @where + ') As TempTable Where RowNo Between ' + STR(@startRowNo,LEN(@startRowNo)) + ' And ' + STR(@endRowNo,LEN(@endRowNo)) End Else --第1页 Set @sql = 'Select Top ' + STR(@pageSize,LEN(@pageSize)) + ' ' + @fieldsList + ' From ' + @tableName + ' Where ' + @where + ' Order By ' + @orderBy --Print @sql Execute sp_executesql @sql Return @@ERROR --返回错误码