预览文章: 存储过程中的分页技巧（最实用的）

大家在开发时都会面临分页的问题，
1.假分页
把数据库的内容全查出来帮到控件上然后再分页显示给用户，效率非常低，2千数据一下凑合还能用，两千条以上就没法用了
2.数据MMSSQl自带的函数number_over
据说效率低，个人感觉有时候也用不成。
3主要介绍第三种，任何时候都刚刚的
ALTER PROCEDURE [dbo].[P_PC_GetList]
-- Add the parameters for the stored procedure here
@Import BIT,
@CurrentPageIndex INT,
@PageSize INT,
@where NVARCHAR(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(4000)
IF @Import=1 BEGIN
SET @sql = 'SELECT [ID]
  ,[PCMonth]
  ,[PCCompany]
  ,[PCDepartment]
  ,[ContractNumber]
  ,[ContractName]
  ,[FirstParty]
  ,[FirstPartyID]
  ,[SignDate]
  ,[SignPersonID]
  ,[SignPerson]
  ,[ResponsiblePersonName]
  ,[ResponsiblePersonId]
  ,[ContractMoney]
  ,[Currency]
  ,[ReceivedMoney]
  ,[TransitAccount]
  ,[PCPossibility]
  ,[MonthPromisePC]
  ,[NoPromiseReason]
  ,[TypeSatus]
  ,[NotComeAmount]
  ,[IsDeleted]
  ,[Remark] FROM T_PC_PaymentCollection'
IF(@where != '')
BEGIN
SET @sql = @sql + ' where  ' + @where
END
EXEC(@sql)
END



DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
IF @CurrentPageIndex = -1 SET @PageLowerBound = 0
ELSE SET @PageLowerBound = @PageSize * @CurrentPageIndex

SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
-- Create a temp table TO store the select results
CREATE TABLE #PageIndexFor_Docu
(
IndexId int IDENTITY (0, 1) NOT NULL,
EID int
)

IF @CurrentPageIndex = -1 AND @PageSize < 2147483647
SET ROWCOUNT @PageSize
    -- Insert statements for procedure here

SET @sql = 'INSERT INTO #PageIndexFor_Docu SELECT [ID]'

IF (@where != '')
BEGIN
SET @sql = @sql + ' where  ' + @where
--Set @sql = @sql + ' where TypeSatus IN (''同步'',''保存'' ) AND ' + @where
END

EXEC (@sql)

SELECT [ID]
,[PCMonth]
,[PCCompany]
,[PCDepartment]
,[ContractNumber]
,[ContractName]
,[FirstParty]
,[FirstPartyID]
,[SignDate]
,[SignPersonID]
,[SignPerson]
,[ResponsiblePersonName]
,[ResponsiblePersonId]
,[ContractMoney]
,[Currency]
,[ReceivedMoney]
,[TransitAccount]
,[PCPossibility]
,[MonthPromisePC]
,[NoPromiseReason]
,[TypeSatus]
,[NotComeAmount]
,[IsDeleted]
,[Remark]
    FROM T_PC_PaymentCollection A
INNER JOIN #PageIndexFor_Docu PIndex
ON PIndex.EID  = A.ID 
WHERE PIndex.IndexId >= @PageLowerBound AND PIndex.IndexId <= @PageUpperBound 
ORDER BY PIndex.IndexId 

END