存储过程中用临时表实现分页

最新推荐文章于 2022-03-27 21:18:59 发布

weixin_30633507

最新推荐文章于 2022-03-27 21:18:59 发布

阅读量120

点赞数

原文链接：http://www.cnblogs.com/Soar1991/archive/2012/03/27/SqlServer-PROCEDURE-Split-Page.html

版权

CREATE PROCEDURE [dbo].[V2_WEB_A_Gis_GetList]
	@FatherId INT,
	@PageIndex INT,
	@PageSize INT,
	@Num INT OUTPUT
AS
BEGIN
	DECLARE @MinId INT
	DECLARE @MaxId INT
	SET @MinId = (@PageIndex - 1) * @PageSize
	SET @MaxId = @MinId + @PageSize
	CREATE TABLE #TempTable
	(
		IndexId INT IDENTITY(1,1) NOT NULL,
		Id INT
	)
	INSERT INTO #TempTable(ID)
		SELECT Id
			FROM Area
			WHERE FatherId = @FatherId
			ORDER BY Id DESC
	SET @Num = (SELECT count(0) From #TempTable)
	SELECT A.* 
		FROM Area A INNER JOIN #TempTable B ON A.Id = B.Id
		WHERE B.IndexId > @MinId AND B.IndexID <=@MaxId
		ORDER BY B.IndexId
	DROP TABLE #TempTable
END

转载于:https://www.cnblogs.com/Soar1991/archive/2012/03/27/SqlServer-PROCEDURE-Split-Page.html