/****** Object:  StoredProcedure [dbo].[proc_SplitPage]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_SplitPage]
 
 @tblName varchar(255),   -- 表名 
 @strFields varchar(255),   -- 顯示字段名 
 @strOrder varchar(255),  -- 排序字段名 
 @strOrderType varchar(50),   -- 設置排序類型, asc || desc
 @PageSize int = 10,   -- 頁尺寸 
 @PageIndex int = 1,   -- 頁碼 
 @strWhere varchar(1000) = ''  -- 查詢條件 (注意: 不要加 where) 
AS 
 declare @strSQL varchar(6000)  -- 主語句 
 declare @strTmp varchar(100)   -- 臨時變量 
 declare @strOrderTemp varchar(400)  -- 排序類型 
if @strOrderType = 'desc' 
 begin 
  set @strTmp = '<(select min' 
  set @strOrderTemp = ' order by [' + @strOrder +'] desc' 
 end 
else 
 begin 
  set @strTmp = '>(select max' 
  set @strOrderTemp = ' order by [' + @strOrder +'] asc' 
 end 
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' 
 + @tblName + '] where [' + @strOrder + ']' + @strTmp + '([' 
 + @strOrder + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 
 + @strOrder + '] from [' + @tblName + ']' + @strOrderTemp + ') as tblTmp)' 
 + @strOrderTemp 
if @strWhere != '' 
 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' 
 + @tblName + '] where [' + @strOrder + ']' + @strTmp + '([' 
 + @strOrder + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 
 + @strOrder + '] from [' + @tblName + '] where ' + @strWhere + ' ' 
 + @strOrderTemp + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderTemp 
if @PageIndex = 1 
 begin 
  set @strTmp = '' 
  if @strWhere != '' 
  set @strTmp = ' where ' + @strWhere 
  set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strFields + ' from [' 
  + @tblName + ']' + @strTmp + ' ' + @strOrderTemp 
 end 
exec (@strSQL) 
GO