分頁SQL 和Oracle 預存程序

CREATE PROCEDURE [dbo].[sp_GetPagingList](
@Table nvarchar(1000),          --表名
@Field nvarchar(1000) = '*',        --讀取欄位
@Where  nvarchar(500) = NULL,       --Where條件
@GroupBy nvarchar(500) = NULL,      --分組
@OrderBy nvarchar(500),       --排序欄位必須
@PageNumber int = 1,            --開始頁碼
@PageSize int = 10,             --頁大小
@IsCount bit = 0            --是否返回記錄總數
)
AS
BEGIN
------------------------------------------------------------------------------------------------
DECLARE @strWhere nvarchar(500)                     --Where 條件
IF @Where IS NOT NULL AND @Where != ''              --Where 條件
BEGIN
SET @strWhere = ' WHERE ' + @Where + ' '
END
ELSE
BEGIN
SET @strWhere = ''
END
----------------------------------------------------------------------------------------------------
DECLARE @strGroupBy nvarchar(500)                   --GroupBy 條件
IF @GroupBy IS NOT NULL AND @GroupBy != ''          --GroupBy 條件
BEGIN
SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' '
END
ELSE
BEGIN
SET @strGroupBy = ''
END
----------------------------------------------------------------------------------------------------
DECLARE @strOrderBy nvarchar(500)                   --OrderBy 條件

SET @strOrderBy = ' ORDER BY ' + @OrderBy

----------------------------------------------------------------------------------------------------
DECLARE @strSql nvarchar(max)   --Sql 語句

----------------------------------------------------------------------------------------------------
IF @PageNumber < 1                                  --第一頁提高效能
BEGIN   
SET @PageNumber = 1
END

IF @PageNumber = 1                          
BEGIN
SET @strSql = 'SELECT TOP ' + str(@PageSize) +  ' ' + @Field + ' FROM ' + @Table + 
   @strWhere + @strGroupBy + @strOrderBy
EXEC sp_executesql @strSql
END 
----------------------------------------------------------------------------------------------------
--根據 SqlServer 2005 協助得到下面的語句 
ELSE
BEGIN 
DECLARE @STARTID nvarchar(50)
DECLARE @ENDID nvarchar(50)
SET @STARTID = convert(nvarchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @ENDID = convert(nvarchar(50),@PageNumber * @PageSize)
SET @strSql = 'WITH MYTABLE AS (SELECT ROW_NUMBER() OVER (' + @strOrderBy + ')
   AS RowNumber,' + @Field + ' FROM '+ @Table +  @strWhere + @strGroupBy + ') 
   SELECT * FROM MYTABLE 
   WHERE RowNumber BETWEEN ' + @STARTID + ' AND ' + @ENDID

  --oracle 分頁語句文法。
  -- select * from (select A.* ,ROWNUM RN from(select * from tb_test)A where ROWNUM<=@ENDID)where RN >=@STARTID
EXEC sp_executesql @strSql
END
--------------------------------------------------------------------------------------------------
--計算總行數
IF @IsCount = 1
BEGIN
SET @strSql= 'SELECT  Count (*) AS RecordCount FROM ' + @Table + @strWhere + @strGroupBy
EXEC sp_executesql @strSql   
END 
RETURN
END
return