首页 新闻 会员 周边 捐助

谁能给个sql server 分页带条件查询的存储过程？

0
悬赏园豆：5 [待解决问题]

迫切需要！！！!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!（最好是实例，详细点的包括调用）

hslgmq的主页 hslgmq | 初学一级 | 园豆：29
提问于：2014-07-10 15:58
< >
分享
所有回答(4)
0

存储过程没有,sql语句有,要不

吴瑞祥 | 园豆：29449 (高人七级) | 2014-07-10 16:06

嗯，来看看

支持(0) 反对(0) hslgmq | 园豆：29 (初学一级) | 2014-07-10 16:07

@hslgmq: 我的博客里``我总共就发了2篇博客其中一篇就是这个分页sql的

支持(0) 反对(0) 吴瑞祥 | 园豆：29449 (高人七级) | 2014-07-10 16:08

@吴瑞祥: 谢谢，但对我没太大用处。分页我会的

支持(0) 反对(0) hslgmq | 园豆：29 (初学一级) | 2014-07-10 16:16
0

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 ' + @TableName + @new_where1
   
    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 TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
                               + @TableName + @new_where1 + @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)
GO

这玩意儿，百度一下一大把

Alex_QY1987 | 园豆：1888 (小虾三级) | 2014-07-10 16:18

好长!!!

支持(0) 反对(0) 吴瑞祥 | 园豆：29449 (高人七级) | 2014-07-10 16:28
0

SQL  server 2012

http://www.cnblogs.com/downmoon/archive/2012/04/12/2443508.html

05,08,08R2

http://www.cnblogs.com/downmoon/archive/2011/02/10/1950789.html

邀月 | 园豆：25475 (高人七级) | 2014-07-10 17:05

两个问题：

支持(0) 反对(0) hslgmq | 园豆：29 (初学一级) | 2014-07-10 17:07

1、sql server 版本是2008，；我要看的是procedure

 

支持(0) 反对(0) hslgmq | 园豆：29 (初学一级) | 2014-07-10 17:10

@hslgmq: 后面的链接页面中间有存储过程和调用

支持(0) 反对(0) 邀月 | 园豆：25475 (高人七级) | 2014-07-10 17:24
0

会写sql分页语句，不会存储过程分页，难道存储过程不要用分页sql语句，只是分页存储过程，将分页的逻辑c#代码改为sql而已

秋壶冰月 | 园豆：5903 (大侠五级) | 2014-07-10 23:08
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。