高效的SQLserver分页存储过程

[有酒有肉头发落]

如题。。。。。。。。。。。。。。。。。。

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('dbo.Proc_PageSearch') IS NOT NULL
	DROP PROC dbo.Proc_PageSearch
GO
-- =============================================
-- Author:		yenange
-- Create date: 2017-12-01
-- Description:	通用分页存储过程
-- =============================================
CREATE PROCEDURE dbo.Proc_PageSearch
	@cols NVARCHAR(MAX)='*'		--哪些列
	,@tableName NVARCHAR(500)='master.dbo.spt_values' --表名或视图名
	,@where NVARCHAR(MAX)='1=1'		--where后面的条件，不要加where
	,@orderby NVARCHAR(MAX)=''		--排序, 不要加order by 
	,@pageIndex INT=1
	,@pageSize  INT=10
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @sqlTotal NVARCHAR(MAX),@sqlGetData NVARCHAR(MAX)
	SET @sqlTotal='select count(1) as cnt from '+@tableName+
		(CASE WHEN @where>'' THEN ' where '+ @where ELSE '' END)

	SET @sqlGetData='select '+@cols+' from '+@tableName+
		(CASE WHEN @where>'' THEN ' where '+ @where ELSE '' END)+
		(CASE WHEN @orderby>'' THEN ' order by '+ @orderby ELSE '' END)
		+' OFFSET '+CAST((@pageIndex-1)*@pageSize AS VARCHAR(50))+' ROWS FETCH NEXT '+CAST(@pageSize AS VARCHAR(50))+' ROWS ONLY '
	--总数
	EXEC(@sqlTotal)
	--数据
	EXEC(@sqlGetData)
END
GO
EXEC Proc_PageSearch
	@cols ='*'		--哪些列
	,@tableName ='master.dbo.spt_values' --表名或视图名
	,@where =' [type]=''P'' and [number]>0 '		--where后面的条件，不要加where
	,@orderby ='[number]'		--排序, 不要加order by 
	,@pageIndex =2
	,@pageSize  =10

注：需要 SQL Server2012或更高版本。 但不推荐搞什么分页存储过程， 表面上省了事， 但实际可能带来SQL注入的风险

[xzxmustwin]

12以上的版本 offset 确实方便啊 不用rownumber写两个select了

[xiaoxiangqing]

2012可以用offset

[一速微光]

已改来一个通用版本的

[zjcxc]

对于复杂的查询来说 想通用，就无法避免性能问题，想要高效，那么就要有针对性的处理 简单地说，不管你是用 ROW_NUMBER，还是有 OFFSET，对于复杂的查询来说，分页都可能落在所有的数据查询完成并排序（如果有要求排序）之后，这个时候分页已经是低效的了 所以通常的做法都是通用分页+重要查询的针对性分页，不是笼统的一种方法