首页 新闻 搜索 专区 学院

sql存储过程分页

0
[已解决问题] 解决于 2017-01-12 07:49

sql存储过程分页该怎么写,不计较效率高低,count 怎么就查不出个数呢,求代码


奔跑的熊猫的主页 奔跑的熊猫 | 初学一级 | 园豆:94
提问于:2017-01-07 22:32
< >
分享
最佳答案
0
USE GISDB_SZTEMP
/*
    分页数据查询通用存储过程
*/
GO
CREATE PROCEDURE [dbo].[PAGINATION]

@FEILDS  VARCHAR(1000),--要显示的数据字段,*表示所有字段
@TABLE_NAME VARCHAR(100),--要查询的数据表名称
@PAGE_INDEX INT,--当前页码
@PAGE_SIZE INT,--页面大小
@ORDERTYPE BIT,--当为0时 则为 desc 当为1 时 asc
@ANDWHERE VARCHAR(1000)='',--where语句 不用加where
@ORDERFEILD VARCHAR(100) --排序的字段
as
DECLARE @EXECSQL VARCHAR(2000)
DECLARE @ORDERSTR VARCHAR(100)
DECLARE @ORDERBY VARCHAR(100)
BEGIN
    set NOCOUNT on
    IF @ORDERTYPE =1 
        BEGIN
            SET @ORDERSTR =' > ( SELECT MAX(['+@ORDERFEILD+'])'
            SET @ORDERBY ='ORDER BY '+@ORDERFEILD+' ASC'
        END
    ELSE 
        BEGIN
            SET @ORDERSTR =' < ( SELECT MIN(['+@ORDERFEILD+'])'
            SET @ORDERBY ='ORDER BY '+@ORDERFEILD+' DESC'
        END

    IF @PAGE_INDEX =1--当页码是第一页时直接运行,提高速度
        BEGIN
            IF @ANDWHERE=''
                SET @EXECSQL ='SELECT TOP '+STR(@PAGE_SIZE)+''+@FEILDS+' FROM '+ @TABLE_NAME +' '+ @ORDERBY
            ELSE
                SET @EXECSQL ='SELECT TOP '+STR(@PAGE_SIZE)+''+@FEILDS+' FROM '+ @TABLE_NAME +' WHERE '+@ANDWHERE+''+ @ORDERBY
        END
    ELSE
        BEGIN
            IF @ANDWHERE=''
                BEGIN      --以子查询结果当做新表时 要给表名别名才能用
                    SET @EXECSQL ='SELECT TOP'+STR(@PAGE_SIZE)+''+@FEILDS+' FROM '+ @TABLE_NAME +' WHERE '+@ORDERFEILD+
                                @ORDERSTR+' FROM (SELECT TOP '+STR(@PAGE_SIZE*(@PAGE_INDEX-1))+''+@ORDERFEILD+
                                ' FROM '+ @TABLE_NAME +' '+@ORDERBY+') AS TEMP) '+ @ORDERBY
                END
            ELSE
                BEGIN
                    SET @EXECSQL ='SELECT TOP'+STR(@PAGE_SIZE)+''+@FEILDS+' FROM '+ @TABLE_NAME +' WHERE '+@ORDERFEILD+
                                @ORDERSTR+' FROM (SELECT TOP '+ STR(@PAGE_SIZE*(@PAGE_INDEX-1))+''+@ORDERFEILD+
                                ' FROM '+ @TABLE_NAME +' WHERE '+@ANDWHERE+''+@ORDERBY+') AS TEMP) AND '+@ANDWHERE+''+ @ORDERBY
                END
        END
EXEC (@EXECSQL)--这里要加括号
END

 

奖励园豆:5
大师兄丶 | 小虾三级 |园豆:843 | 2017-01-08 16:09
其他回答(1)
0

offset-fetch子句

推荐阅读《分页实现:Offset-Fetch

悦光阴 | 园豆:2251 (老鸟四级) | 2017-01-09 10:00
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册