SQL Server 2005 通用分頁存儲(chǔ)過程
來源：程序員人生 發(fā)布時(shí)間：2014-03-04 07:06:49 閱讀次數(shù)：3436次
通過SQLServer 2005之后新加入的ROW_NUMBER()函數(shù)進(jìn)行分頁：
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Description: <通用分頁存儲(chǔ)過程>-- =============================================Create PROCEDURE [dbo].[ClientNewsPager] @talbeName varchar(255)='', -- 表名 @getFields varchar(1000)='*', -- 需要返回的列 @orderBy varchar(255)='', -- 排序的字段名 @PageSize int=15, -- 頁尺寸 @PageIndex int=1, -- 頁碼,從1開始 @doCount bit=0, -- 返回記錄總數(shù), 非 0 值則返回 @whereStr varchar(1500)='' -- 查詢條件 (注意: 不要加 where)ASBEGIN declare @rowResult intBegin Tran declare @strSQL varchar(5000) declare @startRowIndex int declare @maximumRows int set @startRowIndex = (@PageIndex - 1) * @PageSize set @maximumRows = @startRowIndex + @PageSize if(@doCount!=0) begin if @whereStr !='' set @strSQL = 'select id from ' + @talbeName + ' where '+ @whereStr else set @strSQL = 'select id from ' + @talbeName end else begin if(@whereStr!='') begin set @strSQL = 'SELECT ' + @getFields + ' FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @orderBy + ') AS RowsNum,' + @getFields + ' FROM ' + @talbeName + ' where ' + @whereStr + ') AS TMP WHERE (RowsNum > ' + str(@startRowIndex) + ' AND RowsNum <= ' + str(@maximumRows) + ')' end else begin set @strSQL = 'SELECT ' + @getFields + ' FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @orderBy + ') AS RowsNum,' + @getFields + ' FROM ' + @talbeName + ') AS TMP WHERE (RowsNum > ' + str(@startRowIndex) + ' AND RowsNum <= ' + str(@maximumRows) + ')' end end exec(@strSQL) Set @rowResult = @@ROWCOUNTIf @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @rowResult EndEND
