sql server預存程序分頁，支援cte

支援CTE的複雜語句調用：

原始調用SQL語句：

with t as (
select ma_id from sa_affair_info where ad_id=2203 and ai_affair_status=2 and ai_is_pass='true'
),
q as
(
select m.* from SA_Main_Affair m join t on t.ma_id=m.ma_index_no
),p as (
select ai.* from sa_affair_info ai join q on ai.ma_id=q.ma_id where ai_affair_status=2
)
select * from p

 

使用預存程序分頁調用實現同樣效果(支援了cte)：
DECLARE @return_value int,
  @RecordCount int,
  @PAGECOUNT int

EXEC @return_value = [dbo].[Select_Pagination_ex]
  @TableName = N'p',
  @Columns = N'*',
  @CurrentPageIndex = 1,
  @PageSize = 10,
  @RecordCount = @RecordCount OUTPUT,
  @PAGECOUNT = @PAGECOUNT OUTPUT,
  @OrderByColumns = N'ai_id',
  @Where = N'',
  @WITH = N'with t as (
select ma_id from sa_affair_info where ad_id=2203 and ai_affair_status=2 and ai_is_pass=''true''
),
q as
(
select m.* from SA_Main_Affair m join t on t.ma_id=m.ma_index_no
),p as (
select ai.* from sa_affair_info ai join q on ai.ma_id=q.ma_id where ai_affair_status=2
)'

SELECT @RecordCount as N'@RecordCount',
  @PAGECOUNT as N'@PAGECOUNT'

SELECT 'Return Value' = @return_value

GO

-------------------------------------------------------------------------------------------------

簡單查詢語句的調用方法

select * from sa_affair_info where ai_affair_status>0 order by ai_id

調用語句如下：

DECLARE @return_value int,
  @RecordCount int,
  @PAGECOUNT int

EXEC @return_value = [dbo].[Select_Pagination_ex]
  @TableName = N'sa_affair_info',
  @Columns = N'*',
  @CurrentPageIndex = 1,
  @PageSize = 10,
  @RecordCount = @RecordCount OUTPUT,
  @PAGECOUNT = @PAGECOUNT OUTPUT,
  @OrderByColumns = N'ai_id',
  @Where = N'ai_affair_status>0',
  @WITH = N''

SELECT @RecordCount as N'@RecordCount',
  @PAGECOUNT as N'@PAGECOUNT'

SELECT 'Return Value' = @return_value

GO

-------------------------------------------------------------------------------------------------

分頁預存程序源碼：

 

 

--=============================================================================================
--單表查詢調用
--****************************************************************************************************
--原始查詢語句
--SELECT Addressid,AddressLine1,City FROM [AdventureWorks].[Person].[Address] where (2=2 OR 3=3) AND Addressid > 3000
--****************************************************************************************************
--DECLARE @return_value int,
--  @PAGECOUNT int,
--  @RECORDCOUNT INT
--EXEC @return_value = [dbo].[Select_Pagination]
--  @TableName = N'[AdventureWorks].[Person].[Address]',
--  @Columns = N'Addressid,AddressLine1,City',
--  @CurrentPageIndex = 1,
--  @PageSize = 10,
--  @RecordCount = @RecordCount OUTPUT,
--  @PAGECOUNT = @PAGECOUNT OUTPUT,
--  @OrderByColumnS = N'Addressid Asc',
--  @WHERE = N'(2=2 OR 3=3) AND Addressid > 3000'
--SELECT @PAGECOUNT as N'@PAGECOUNT'
--SELECT @RecordCount as N'@RECORDCOUNT'
--SELECT 'Return Value' = @return_value
--GO
--***************************************************************************************
--串連查詢調用
--****************************************************************************************************
--原始查詢語句
--select [CustomerID],[TerritoryID],[AccountNumber],[CustomerType],[rowguid],[ModifiedDate],CustomerType.[Name]
--FROM [AdventureWorks].[Sales].[Customer] join customertype on [Sales].[Customer].CustomerType = CustomerType.ID
--order by [Sales].[Customer].ModifiedDate desc,[Sales].[Customer].CustomerID DESC
--****************************************************************************************************
--USE [AdventureWorks]
--GO
--DECLARE @return_value int,
--  @PAGECOUNT int,
--  @RECORDCOUNT INT
--EXEC @return_value = [dbo].[Select_Pagination]
--  @TableName = N'[AdventureWorks].[Sales].[Customer] join customertype on [Sales].[Customer].CustomerType = CustomerType.ID',
--  @Columns = N'[CustomerID],[TerritoryID],[AccountNumber],[CustomerType],[rowguid],[ModifiedDate],CustomerType.[Name]',
--  @CurrentPageIndex = 1916,
--  @PageSize = 10,
--  @RecordCount = @RecordCount OUTPUT,
--  @PAGECOUNT = @PAGECOUNT OUTPUT,
--  @OrderByColumnS = N'[Sales].[Customer].ModifiedDate desc,[Sales].[Customer].CustomerID DESC',
--  @wHERE = N''
--SELECT @PAGECOUNT as N'@PAGECOUNT'
--SELECT @RecordCount as N'@RECORDCOUNT'
--SELECT 'Return Value' = @return_value
--GO
--=============================================================================================

ALTER PROC [dbo].[Select_Pagination_ex](
@TableName nVARCHAR(4000),
@Columns nVARCHAR(4000),
@CurrentPageIndex INT,
@PageSize INT,
@RecordCount INT OUTPUT,
@PAGECOUNT INT OUTPUT,
@OrderByColumns nVARCHAR(1000),
@Where NVarchar(4000),
@WITH NVARCHAR(4000)--定義通用運算式，
) AS
BEGIN
DECLARE @COUNT_SQL NVARCHAR(4000)
DECLARE @ParmDefinition NVARCHAR(1000)
SET @ParmDefinition = N'@COUNT INT OUTPUT';
IF @WITH <> N''
 SET @COUNT_SQL = @WITH + N'SELECT @COUNT=COUNT(*) FROM ' + @tablename + N' where 1 = 1 '
ELSE
 SET @COUNT_SQL = N'SELECT @COUNT=COUNT(*) FROM ' + @tablename + N' where 1 = 1 '

IF @WHERE <> N''
  SET @COUNT_SQL = @COUNT_SQL + N' AND (' + @Where + N')'
--PRINT @COUNT_SQL
EXECUTE SP_EXECUTESQL @COUNT_SQL,@ParmDefinition,@COUNT=@RecordCount OUTPUT;
IF (@RecordCount % @PageSize) > 0
  SET @PageCount = @RecordCount / @PageSize + 1
ELSE
  SET @PageCount = @RecordCount / @PageSize
Declare @SQL NVARCHAR(4000)
IF @WITH <> N''
BEGIN
 SET @SQL = @WITH
 SET @Sql = @SQL + N', TMPTABLE as('
END
ELSE
 SET @Sql = N'WITH TMPTABLE as('
set @sql = @sql + N'select ' + @columns + N',ROW_NUMBER() over(order by '
set @Sql = @sql + @orderByColumns
set @sql = @sql + N') ROWNO FROM '
SET @SQL = @SQL + @TABLENAME + N' WHERE 1 = 1 '
IF @WHERE <> N''
  SET @SQL = @SQL + N' AND (' + @WHERE + N')'
SET @SQL = @SQL + N')'
DECLARE @BEGINNO INT
DECLARE @ENDNO INT
SET @BEGINNO = (@CURRENTPAGEINDEX - 1) * @PAGESIZE + 1
SET @ENDNO = @BEGINNO + @PAGESIZE - 1
IF @ENDNO > @RecordCount
  SET @ENDNO = @RecordCount
SET @SQL = @SQL + N'SELECT * FROM TMPTABLE WHERE ROWNO >= ' + CONVERT(NVARCHAR(5), @BEGINNO)
  + N' AND ROWNO <=' + CONVERT(NVARCHAR(5), @ENDNO)

EXEC SP_EXECUTESQL @SQL
END