panjamin
7/15/2018 - 3:07 AM

分页SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_名称_List] 
--参数

--分页
@offset int,
@pageSize int,
@sort  nvarchar(100),
@order nvarchar(100)

AS Begin SET NOCOUNT ON; 
declare @ordersort nvarchar(200) 
set @ordersort=LOWER(@sort+'_'+@order)
	
;WITH Main_CTE AS	
(
	 --begin sql查询语句
	 
	 --end sql查询语句
)
   ,Count_CTE AS (
    SELECT COUNT(*) AS [Data_TotalCount]
    FROM Main_CTE
)
	SELECT *
	FROM Main_CTE,Count_CTE
	ORDER BY 
			CASE WHEN @ordersort=LOWER('CreateTime_asc') THEN CreateTime END asc,
			CASE WHEN @ordersort=LOWER('SimpleName_asc') THEN SimpleName END asc,
			CASE WHEN @ordersort=LOWER('CreateTime_desc') THEN CreateTime END desc,
			CASE WHEN @ordersort=LOWER('SimpleName_desc') THEN SimpleName END desc,
			CASE WHEN @ordersort='' THEN CreateTime END asc
	OFFSET @offset ROWS
	FETCH NEXT @pageSize ROWS ONLY
 END