灵活的分页存储过程

ALTER procedure [dbo].[分页]
@pageSize  int,
@pageOrder int,
@searchTable NVARCHAR(200),
@pageAmount INT OUTPUT
AS
BEGIN
    DECLARE @STR NVARCHAR(MAX)

    SET @STR = '
                 set @pageAmount =(select COUNT(*)/@pageSize
                  from @searchTable)
               
                select top (@pageSize) *
    from @searchTable
    where fid not in
    (
     select top (@pageSize * (@pageOrder-1)) fid
     from @searchTable
     ORDER BY FID
    )
    ORDER BY FID
                '
    SET @STR = REPLACE(@STR,'@pageSize',@pageSize)
    SET @STR = REPLACE(@STR,'@pageOrder',@pageOrder)
    SET @STR = REPLACE(@STR,'@searchTable',@searchTable)
    --SET @STR = REPLACE(@STR,'@pageAmount',@pageAmount)
   
    PRINT @STR
   

    EXECUTE sp_ExecuteSql @STR, N'@pageAmount INT OUTPUT', @pageAmount OUTPUT

END

 

 

下面是调用过程，当传递的是连接的多表，临时表时，应该，在表名上加 （）

 

DECLARE @pageAmountA INT

SET @pageAmountA = 0

EXEC [dbo].[PageDiv]
    @pageSize = 25,
    @pageOrder = 1,
    @searchTable = '最终行政区划',
    --@searchTable = '(SELECT A.*,B.* FROM 最终行政区划 A LEFT JOIN YZ_User B on 1 =1)',
    @pageAmount = @pageAmountA OUTPUT

SELECT @pageAmountA

 

 

 

 

 

 

 

 

单独获取页面数量的存储过程

USE [AiXinTang]
GO
/****** Object:  StoredProcedure [dbo].[GetPageAmount]    Script Date: 06/17/2013 11:10:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetPageAmount]
@pageSize  int,
@searchTable NVARCHAR(200),
@pageAmount INT OUTPUT
AS
BEGIN
  DECLARE @STR NVARCHAR(MAX)

    SET @STR = '
                 set @pageAmount =(select COUNT(*)/@pageSize
                  from @searchTable as D)

                
   
         
                '
    SET @STR = REPLACE(@STR,'@pageSize',@pageSize)
    SET @STR = REPLACE(@STR,'@searchTable',@searchTable)
    --SET @STR = REPLACE(@STR,'@pageAmount',@pageAmount)
   
    PRINT @STR
    EXECUTE sp_ExecuteSql @STR ,N'@pageAmount INT OUTPUT', @pageAmount OUTPUT
END