SQL分页存储过程——表名、返回的列、排序字段、排序类型、条件、页尺寸、页码

ALTER PROCEDURE [dbo].[SP_LGY_ICU_PAGECUT]
@TableName varchar(255), -- 表名
@DisplayFields varchar(1000) = '*', -- 需要返回的列 
@OrderFields varchar(255)='''', -- 排序的欄位名
@OrderType bit = 0, -- 設置排序類型, 非 0 值則降冪
@StrWhere varchar(1500) = '''', -- 查詢條件 (注意: 不要加 where)
@PageSize int = 10, -- 頁尺寸
@PageIndex int OUTPUT, -- 頁碼
@AllRowCount int OUTPUT,
@AllPageCount int OUTPUT

AS

declare @strSQL nvarchar(4000) -- 主語句
declare @strTmp nvarchar(110) -- 臨時變數
declare @strOrder nvarchar(400) -- 排序類型


begin
    declare   @d   datetime   
    set   @d=getdate() 
    if @StrWhere !=''''
        set @strSQL = N'select @AllRowCount=count(*)  from [' + @TableName + '] with(nolock) where '+@StrWhere 
    else
        set @strSQL =N'select @AllRowCount=count(*)  from [' + @TableName + ']'+' with(nolock) '
    exec sp_executesql @strSQL,N'@AllRowCount int out',@AllRowCount out

    IF ISNULL(@PageSize,0)<1
       SET @PageSize=10
    IF @AllPageCount % @PageSize=0
       SET @AllPageCount=@AllRowCount/@PageSize
    ELSE IF @PageSize=1
       SET @AllPageCount=@AllRowCount/@PageSize
    ELSE
       SET @AllPageCount=@AllRowCount/@PageSize+1
    IF ISNULL(@PageIndex,0)<1
       SET @PageIndex=1
    ELSE IF ISNULL(@PageIndex,0)>@AllPageCount
       SET @PageIndex=@AllPageCount

    if @OrderType != 0--采用升降序的何種方式排序，不是0則降序
    begin
        set @strTmp = '<(select min'
        set @strOrder = ' order by ' + @OrderFields +' desc'
    end
    else--是0則升序,默認為升序
    begin
        set @strTmp = '>(select max'
        set @strOrder = ' order by ' + @OrderFields +' asc'
    end
    
    if @PageIndex = 1
    begin
        if @StrWhere != ''''
        set @strSQL = 'select top ' + str(@PageSize) +' '+@DisplayFields+ 'from [' + @TableName + '] with(nolock) where ' + @StrWhere + ' ' + @strOrder
        else
        set @strSQL ='select top '+ str(@PageSize) +' '+@DisplayFields+ 'from ['+ @TableName + '] with(nolock) '+ @strOrder
        --如果是第一頁就執行以上代碼，這樣會加快執行速度
    end
    else
    begin
        --以下代碼賦予了@strSQL以真正執行的SQL代碼
        set @strSQL = 'select top ' + str(@PageSize) +' '+@DisplayFields+ ' from ['+ @TableName + '] with(nolock) where ' + @OrderFields + '' + @strTmp + '(tabKey) from (select top '+ str((@PageIndex-1)*@PageSize) + ' '+ @OrderFields + ' as tabKey from [' + @TableName + '] with(nolock) ' + @strOrder + ') as tblTmp)'+ @strOrder
        if @StrWhere != ''''
        set @strSQL = 'select top ' + str(@PageSize) +' '+@DisplayFields+ ' from ['+ @TableName + '] with(nolock) where ' + @OrderFields + '' + @strTmp + '(tabKey) from (select top '+ str((@PageIndex-1)*@PageSize) + ' '+ @OrderFields + ' as tabKey from [' + @TableName + '] with(nolock) where ' + @StrWhere + ' '+ @strOrder + ') as tblTmp) and ' + @StrWhere + ' ' + @strOrder
    end
end
Print @StrSql
exec (@strSQL)