欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

存储过程中的分页技巧(最实用的)

程序员文章站 2023-12-22 10:11:28
...
大家在开发时都会面临分页的问题,
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

上一篇:

下一篇: