SQLServer 2005通用分頁儲存過程

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtset ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:        
-- Description:    
-- =============================================
Create PROCEDURE [dbo].[ClientNewsPager]
    @talbeName   varchar(255)='',             -- 表名
    @getFields varchar(1000)='*',       -- 需要返回的列
    @orderBy varchar(255)='',          -- 排序的欄位名
    @PageSize   int=15,                 -- 頁尺寸
    @PageIndex  int=1,               -- 頁碼,從1開始
    @doCount  bit=0,           -- 返回記錄總數, 非 0 值則返回
    @whereStr  varchar(1500)=''  -- 查詢條件 (注意: 不要加 where)
AS
BEGIN
    declare @rowResult int

Begin 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 = @@ROWCOUNT

If @@Error <> 0
    Begin
        RollBack Tran
        Return -1
    End
    Else
    Begin
        Commit Tran
        Return @rowResult
    End
END