存储过程分页

-- =============================================
-- Author:        <Author,,microant>
-- Create date: <Create Date,,20070705>
-- Description:    <Description,,CTE分页>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CTE]( 
    -- Add the parameters for the stored procedure here
    @TableName nvarchar(200) = 'testTable',    --表名
    @PageSize int = 15,        --页面大小
    @PageIndex int =2    ,    --页面的序号
    @IsAsc bit = 1    ,    --是否卫升序,升序为1，降序为0
    @OderColumName nvarchar(200) = null,        --排序字段名
    @KeyID nvarchar(50) ,        --主键
    @Conditions nvarchar(500) = null --查询条件
)
AS

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @strSql nvarchar(1000)
    declare @tempstr nvarchar(1000)
    declare @orderstr nvarchar(400)
    declare @ctestr nvarchar(400)
    
    --判断排序方式,@IsAsc =1 升序, 0降序,设置排序语句
    if @IsAsc = 1
    begin
        if(@OderColumName is null or @OderColumName = '')
            set @orderstr = ' order by ' + @KeyID + ' asc'
        else
            set @orderstr = ' order by ' + @OderColumName + ' asc'
    end
    else
    begin
        if(@OderColumName is null or @OderColumName = '')
            set @orderstr = ' order by ' + @KeyID + ' desc'
        else
            set @orderstr = ' order by ' + @OderColumName + ' desc'
    end
    
    --CTE
    set @ctestr ='with Table_CET
    as
    (
        select 
            CEILING((ROW_NUMBER() OVER (' + @orderstr + '))/' + str(@PageSize) + ') as page_num, * 
        from ' + @TableName +
    ')' ;
    
begin
    if(@Conditions is null or @Conditions = '')
        set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex);
    else
        set @strSql = @ctestr + 'select * from Table_CET where page_num = ' + str(@PageIndex)+ ' and ' + @Conditions;
    print @strSql
end    
    begin
        exec sp_executesql  @strSql
    end