存储过程分页

caizhuguanguan 2010-08-26 04:46:01

--简历浏览查询(Per_View_Record表和Com_Post_Job)
ALTER proc [dbo].[pro_SelectJianLiLiuLan]
as
begin
SELECT a.ViewDate,a.ViewCompanyName,b.JobAddress,b.JobAddDate FROM Per_View_Record a,Com_Post_Job b
WHERE a.ViewComID=b.ComJobID
return
end

这个查询的过程怎么写个存储过程分页啊？
帮我改改写个谢谢了

...全文

124 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

哥子谭 2010-08-31

打赏
举报



/*通用分页存储过程*/

USE HotelManagementSystem

GO

IF EXISTS(SELECT * FROM sys.objects WHERE NAME='cndoup_GetPageOfRecords')

  DROP PROCEDURE cndoup_GetPageOfRecords

GO

--创建存储过程

CREATE PROCEDURE cndoup_GetPageOfRecords

  @pageSize int = 20,                                                --分页大小

  @currentPage int ,                                                 --第几页

  @columns varchar(1000) = '*',                            --需要得到的字段 

  @tableName varchar(100),                                     --需要查询的表    

  @condition varchar(1000) = '',                         --查询条件, 不用加where关键字

  @ascColumn varchar(100) = '',                            --排序的字段名 (即 order by column asc/desc)

  @bitOrderType bit = 0,                                         --排序的类型 (0为升序,1为降序)

  @pkColumn varchar(50) = ''                                 --主键名称



AS

BEGIN                                                                                    --存储过程开始

  DECLARE @strTemp varchar(300)

  DECLARE @strSql varchar(5000)                            --该存储过程最后执行的语句

  DECLARE @strOrderType varchar(1000)                --排序类型语句 (order by column asc或者order by column desc)



  BEGIN

    IF @bitOrderType = 1                --降序

      BEGIN

        SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'

        SET @strTemp = '<(SELECT min'

      END

    ELSE                  --升序

      BEGIN

        SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'

        SET @strTemp = '>(SELECT max'

      END



    IF @currentPage = 1            --第一页

      BEGIN

        IF @condition != ''

          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+

            ' WHERE '+@condition+@strOrderType

        ELSE

          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+@strOrderType

      END



    ELSE                  -- 其他页

      BEGIN

        IF @condition !=''

          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+

          ' WHERE '+@condition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+

          ' '+@pkColumn+' FROM '+@tableName'where'+@condition+@strOrderType+') AS TabTemp)'+@strOrderType

        ELSE

          SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@columns+' FROM '+@tableName+

          ' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currentPage-1)*@pageSize)+' '+@pkColumn+

          ' FROM '+@tableName+@strOrderType+') AS TabTemp)'+@strOrderType

      END



  END

  EXEC (@strSql)

END

永生天地 2010-08-26

打赏
举报

分页整理
http://blog.csdn.net/xys_777/archive/2010/08/06/5792284.aspx

王向飞 2010-08-26

打赏
举报

ALTER proc [dbo].[pro_SelectJianLiLiuLan]

@pageNum int, --页码

@pageSize int --每页条数

as

begin

;with t as (

SELECT ROW_NUMBER() over (order by a.ViewDate) as id,

a.ViewDate,a.ViewCompanyName,b.JobAddress,b.JobAddDate 

FROM Per_View_Record a,Com_Post_Job b

WHERE a.ViewComID=b.ComJobID

)

select t.* from t

WHERE t.id BETWEEN (@pageNum-1)*@pageSize+1 AND @pageNum*@pageSize 

ORDER BY t.id DESC

end

exec pro_SelectJianLiLiuLan 1,10