SQL 存储过程分页

CREATE PROC p_Team_GetTemaList

    @pageindex INT ,

    @pagesize INT ,

    @keywords VARCHAR(200) , --模糊查询 名称 标签

    @citycode VARCHAR(100) ,

    @verifyStatus  INT =0,-- -1所有的 0 没有通过 1 审核通过

    @orderby INT,

    @TotalCount INT OUTPUT

AS

    BEGIN

        DECLARE @PageLowerBound INT

        DECLARE @PageUpperBound INT

        DECLARE @Where  VARCHAR(300)=''

        DECLARE @Order  VARCHAR(300)=''

        DECLARE @exsql NVARCHAR(1000)=''

        SET @PageLowerBound = @PageSize * ( @PageIndex - 1 )

        SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

        --条件

        SET @Where=' WHERE 1=1 '

			IF @verifyStatus>=0 --条件 数字

				BEGIN

					SET @Where =@Where+' and verifyStatus = '+CAST(@verifyStatus AS VARCHAR(200))

				END

                        IF LEN(@citycode)>0

			       BEGIN

				 SET @Where =@Where+' AND StartCityCode = '''+CAST(@citycode AS VARCHAR(50))+''''

			       END

			IF LEN(@citycode)>0 --条件字符串

				BEGIN

					SET @Where =@Where+' and City  LIKE ''%'+CAST(@citycode AS VARCHAR(200))+'%'''

				END

			IF LEN(@keywords)>0 --模糊查询

				BEGIN

					SET @Where =@Where+' and ('

					SET @Where =@Where+'  TeamName  LIKE ''%'+CAST(@keywords AS VARCHAR(200))+'%'''

					SET @Where =@Where+' or Slogan  LIKE ''%'+CAST(@keywords AS VARCHAR(200))+'%'''

					SET @Where =@Where+' ) '

				END

			--排序

			IF  @orderby =0

				BEGIN

					SET @Order= N' ORDER BY IsShowIndex DESC, CreateTime DESC '

				END

			ELSE

				BEGIN

					SET @Order= N' ORDER BY IsShowIndex ASC, CreateTime ASC '

				END 　　　　　　　　--创建临时表

		 CREATE TABLE #data  (   RowNo int IDENTITY (0, 1) NOT NULL,   KeyID	int not null )

			SET @exsql=N'INSERT INTO #data(KeyID) SELECT  TeamId FROM t_Team_TeamList ' +@Where +@Order

			--SELECT @exsql

			EXEC sp_executesql  @exsql

			SET @TotalCount = (SELECT COUNT(1) FROM #data)

			SELECT * FROM dbo.t_Team_TeamList t1 JOIN  #data e  ON t1.TeamId=e.KeyID

			WHERE e.RowNo >= @PageLowerBound AND e.RowNo <= @PageUpperBound

			ORDER BY e.RowNo ASC

    END

--第二种简单点的分业

CREATE  PROC p_PC_GetLeaveCommentsList

    @pageindex INT ,

    @pagesize INT ,

    @TotalCount INT OUTPUT

AS

    BEGIN

        SELECT @TotalCount=COUNT(0) FROM dbo.t_Comm_LeaveComments

        ;WITH cte AS(

				SELECT RowNumber = ROW_NUMBER() OVER(ORDER BY CreateTS DESC ),*

			   FROM  dbo.t_Comm_LeaveComments

        )

        SELECT * FROM cte

         WHERE RowNumber BETWEEN (@pageindex - 1) * @pageSize + 1 AND @pageindex * @pageSize

	ORDER BY RowNumber

    END