存储过程分页(1)

不点 阅读:200 2022-02-09 13:16:27 评论:0
create PROCEDURE Sp_Conn_Sort 
( 
@tblName   varchar(255),       -- 表名 
 
@strGetFields varchar(1000) = '*',  -- 需要返回的列 
 
@fldName varchar(255)='',      -- 排序的字段名 
 
@PageSize   int = 40,          -- 页尺寸 
 
@PageIndex  int = 1,           -- 页码 
 
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回 
 
@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序 
@strWhere  varchar(1500)=''  -- 查询条件 (注意: 不要加 where) 
) 
AS 
 
declare @strSQL   varchar(5000)       -- 主语句 
 
declare @strTmp   varchar(110)        -- 临时变量 
 
declare @strOrder varchar(400)        -- 排序类型 
 
  
 
if @doCount != 0 
 
  begin 
 
    if @strWhere !='' 
 
    set @strSQL = 'select count(*) as Total from ' + @tblName + ' where '+@strWhere 
 
    else 
 
    set @strSQL = 'select count(*) as Total from ' + @tblName 
 
end  
 
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。以下的所有代码都是@doCount为0的情况 
 
else 
 
begin 
 
  
 
if @OrderType != 0 
 
begin 
 
    set @strTmp = '<(select min' 
 
set @strOrder = ' order by ' + @fldName +' desc' 
 
--如果@OrderType不是0,就执行降序,这句很重要! 
 
end 
 
else 
 
begin 
 
    set @strTmp = '>(select max' 
 
    set @strOrder = ' order by ' + @fldName +' asc' 
 
end 
 
  
 
if @PageIndex = 1 
 
begin 
 
    if @strWhere != ''   
 
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder 
 
     else 
 
     set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from '+ @tblName + ' '+ @strOrder 
 
--如果是第一页就执行以上代码,这样会加快执行速度 
 
end 
 
else 
 
begin 
 
--以下代码赋予了@strSQL以真正执行的SQL代码 
 
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' 
 
    + @tblName + ' where ' + @fldName + '' + @strTmp + '('+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder 
 
  
 
if @strWhere != '' 
 
    set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ '  from ' 
 
        + @tblName + ' where ' + @fldName + '' + @strTmp + '(' 
 
        + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' 
 
        + @fldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' 
 
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
 
end 
 
end   
 
exec (@strSQL) 
 
-----------------

sql server 2000 单主键高效分页存储过程 (支持多字段排序)

Create PROC P_viewPage          
    /*           
    nzperfect [no_mIss] 高效通用分页存储过程(双向检索) 2007.5.7  QQ:34813284           
    敬告:适用于单一主键或存在唯一值列的表或视图           
    ps:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围           
    */           
    @TableName VARCHAR(200),     --表名           
    @FieldList VARCHAR(2000),    --显示列名,如果是全部字段则为*           
    @PrimaryKey VARCHAR(100),    --单一主键或唯一值键           
    @Where VARCHAR(2000),        --查询条件 不含'where'字符,如id>10 and len(userid)>9           
    @Order VARCHAR(1000),        --排序 不含'order by'字符,如id asc,userid desc,必须指定asc或desc           
    --注意当@SortType=3时生效,记住一定要在最后加上主键,否则会让你比较郁闷           
    @SortType INT,               --排序规则 1:正序asc 2:倒序desc 3:多列排序方法           
    @RecorderCount INT,          --记录总数 0:会返回总记录           
    @PageSize INT,               --每页输出的记录数           
    @PageIndex INT,              --当前页数           
    @TotalCount INT OUTPUT ,      --记返回总记录           
    @TotalPageCount INT OUTPUT   --返回总页数           
AS           
SET NOCOUNT ON           
    IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0           
    SET @Order = RTRIM(LTRIM(@Order))           
    SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))           
    SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')           
    WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0           
        BEGIN           
            SET @Order = REPLACE(@Order,', ',',')           
            SET @Order = REPLACE(@Order,' ,',',')           
        END           
    IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = ''           
            or ISNULL(@PrimaryKey,'') = ''           
            or @SortType < 1 or @SortType >3           
            or @RecorderCount  < 0 or @PageSize < 0 or @PageIndex < 0           
        BEGIN           
            PRINT('ERR_00')           
            RETURN           
        END           
    IF @SortType = 3           
        BEGIN           
            IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')           
                BEGIN PRINT('ERR_02') RETURN END           
        END           
    DECLARE @new_where1 VARCHAR(1000)           
    DECLARE @new_where2 VARCHAR(1000)           
    DECLARE @new_order1 VARCHAR(1000)           
    DECLARE @new_order2 VARCHAR(1000)           
    DECLARE @new_order3 VARCHAR(1000)           
    DECLARE @Sql VARCHAR(8000)           
    DECLARE @SqlCount NVARCHAR(4000)           
    IF ISNULL(@where,'') = ''           
        BEGIN           
            SET @new_where1 = ' '           
            SET @new_where2 = ' Where  '           
        END           
    ELSE           
        BEGIN           
            SET @new_where1 = ' Where ' + @where           
            SET @new_where2 = ' Where ' + @where + ' AND '           
        END           
    IF ISNULL(@order,'') = '' or @SortType = 1  or @SortType = 2           
        BEGIN           
            IF @SortType = 1           
                BEGIN           
                    SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC'           
                    SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC'           
                END           
            IF @SortType = 2           
                BEGIN           
                    SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC'           
                    SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC'           
                END           
        END           
    ELSE           
        BEGIN           
            SET @new_order1 = ' orDER BY ' + @Order           
        END           
     
    IF @SortType = 3 AND  CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0           
    BEGIN           
        SET @new_order1 = ' orDER BY ' + @Order           
        SET @new_order2 = @Order + ','           
        SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')           
        SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')           
        SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)           
        IF @FieldList <> '*'           
            BEGIN           
                SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')           
                SET @FieldList = ',' + @FieldList           
                WHILE CHARINDEX(',',@new_order3)>0           
                    BEGIN           
                        IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0           
                            BEGIN           
                                SET @FieldList =           
                                @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))           
                            END           
                        SET @new_order3 =           
                        SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))           
                    END           
                SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))           
            END           
        END      
          
    SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'           
    + CAST(@PageSize AS VARCHAR)+') FROM (Select * FROM ' + @TableName + @new_where1+') AS T'           
    IF @RecorderCount  = 0           
        BEGIN           
            EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',           
            @TotalCount OUTPUT,@TotalPageCount OUTPUT           
        END           
    ELSE           
        BEGIN           
            Select @TotalCount = @RecorderCount         
        END           
    IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)           
        BEGIN           
            SET @PageIndex =  CEILING((@TotalCount+0.0)/@PageSize)           
        END           
    IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)           
        BEGIN           
            IF @PageIndex = 1 --返回第一页数据           
                BEGIN           
                    SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '           
                    + @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1     
                END           
            IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)  --返回最后一页数据           
                BEGIN           
                    SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('           
                    + 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))           
                    + ' ' + @FieldList + ' FROM '           
                    + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '           
                    + @new_order1           
                END           
        END           
    ELSE       
             
        BEGIN           
        IF @SortType = 1  --仅主键正序排序           
            BEGIN           
                IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索           
                    BEGIN           
                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '           
                        + @TableName + @new_where2 + @PrimaryKey + ' > '           
                        + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '           
                        + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey           
                        + ' FROM ' + @TableName           
                        + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1           
                    END           
                ELSE  --反向检索           
                    BEGIN           
                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('           
                        + 'Select TOP ' + STR(@PageSize) + ' '           
                        + @FieldList + ' FROM '           
                        + @TableName + @new_where2 + @PrimaryKey + ' < '           
                        + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '          
                        + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey           
                        + ' FROM ' + @TableName           
                        + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2           
                        + ' ) AS TMP ' + @new_order1           
                    END           
            END           
        IF @SortType = 2  --仅主键反序排序           
            BEGIN           
                IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索           
                    BEGIN           
                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '           
                        + @TableName + @new_where2 + @PrimaryKey + ' < '           
                        + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '           
                        + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey           
                        +' FROM '+ @TableName           
                        + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1           
                    END           
                ELSE  --反向检索           
                    BEGIN           
                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('           
                        + 'Select TOP ' + STR(@PageSize) + ' '           
                        + @FieldList + ' FROM '           
                        + @TableName + @new_where2 + @PrimaryKey + ' > '           
                        + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '           
                        + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey           
                        + ' FROM ' + @TableName           
                        + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2           
                        + ' ) AS TMP ' + @new_order1           
                    END           
            END           
        IF @SortType = 3  --多列排序,必须包含主键,且放置最后,否则不处理           
            BEGIN           
                IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0           
                    BEGIN PRINT('ERR_02') RETURN END           
                    IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2  --正向检索           
                        BEGIN           
                            SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '           
                            + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '           
                            + ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList           
                            + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '           
                            + @new_order2 + ' ) AS TMP ' + @new_order1           
                        END           
                    ELSE  --反向检索           
                        BEGIN           
                            SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '           
                            + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '           
                            + ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList           
                            + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '           
                            + @new_order1 + ' ) AS TMP ' + @new_order1           
                        END           
            END           
        END           
    PRINT(@SQL)           
    EXEC(@Sql)

标签:数据库
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

搜索
排行榜
关注我们

一个IT知识分享的公众号