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