USE [QSM]
GO
/****** Object: StoredProcedure [dbo].[UP_SysGetListByPage] Script Date: 04/12/2017 17:47:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: 分頁存儲過程(只支技MSSQL2005及以上版本)
-- =============================================
ALTER PROCEDURE [dbo].[Sp_GetListByPage]
@Select nvarchar(1000) --要查詢的字段(已含SELECT)
,@From nvarchar(1000) --表名 (已含From)
,@WhereSql nvarchar(1000) --查詢條件 (已含Where 1=1 )
,@GroupBy nvarchar(250) --分組 (已含Group By)
,@Having nvarchar(250) --過濾條件 (已含Having)
,@OrderBy nvarchar(500) --排序字段 (已含Order By)
,@IsDesc bit=0 --排序： 0=ASC,1=DESC
,@PageIndex int = 0 --頁索引： 0=第1頁
,@PageSize int = 10 --頁容量： 默認10行
,@IsRecord bit =0 --0=數據集查詢，1=取記錄數
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlFrom nvarchar(3000); --要查詢的字段及表
DECLARE @pagedSql nvarchar(max); --拼接后的總SQL語句
SET @sqlFrom=N' from ' + @From;
IF(LEN(@WhereSql)>0) SET @sqlFrom+=' where 1=1 ' + @WhereSql;
IF(LEN(@GroupBy)>0) SET @sqlFrom+=' group by ' + @GroupBy;
IF(LEN(@Having)>0) SET @sqlFrom+=' having ' + @Having;
IF(@IsDesc=1) SET @OrderBy+=' DESC ';
SET @pagedSql=
N'with PagedList as '
+' ( '
+' select top 100 percent '+@Select
+ ' , row_number() over (order by '+@OrderBy+') as mydata_RowNumber '
+ @sqlFrom
+' ) '
+' select * '
+' from PagedList '
+' where mydata_RowNumber between ' + STR(@PageIndex*@PageSize+1)
+ ' and ' + STR(@PageIndex*@PageSize+@PageSize) ;
IF @IsRecord =1
SET @pagedSql = 'select count(1) as [RowCount] from ' + @From +' where 1=1 ' + @WhereSql ;
EXEC(@pagedSql)
--SELECT @pagedSql --生成的語句
END
