SQL 分頁儲存過程

 
GO

/****** Object:  StoredProcedure [dbo].[UP_GetRecordByPage]    Script Date: 2020-09-24 13:20:25 ******/
DROP PROCEDURE [dbo].[UP_GetRecordByPage]
GO

/****** Object:  StoredProcedure [dbo].[UP_GetRecordByPage]    Script Date: 2020-09-24 13:20:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/****** Object:  StoredProcedure [dbo].[UP_GetRecordByPage]    Script Date: 04/14/2015 16:57:36 ******/
--IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UP_GetRecordByPage]') AND type in (N'P', N'PC'))
--DROP PROCEDURE [dbo].[UP_GetRecordByPage]
--GO

CREATE PROCEDURE [dbo].[UP_GetRecordByPage]
(
@tblName   varchar(255),       -- 表名
@strGetFields varchar(4000) = '*',  -- 需要返回的列
@fldName varchar(255)='',      -- 排序的欄位名
@PageSize   int = 10,          -- 頁尺寸
@PageIndex  int = 1,           -- 頁碼
@IsReCount  bit = 0,   -- 返回記錄總數, 非 0 值則返回
@OrderType  int = 0,  -- 設定排序型別, 0升序、1降序、2任意欄位的升和降，需要注意傳入值否則查詢出錯
@strWhere  varchar(1500) = ''  -- 查詢條件 (注意: 不要加 where)
)
AS

declare @strSQL   varchar(8000)       -- 主語句
declare @strOrder varchar(5000)        -- 排序型別

if @IsReCount != 0
    begin
        if @strWhere !=''
            set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere
        else
            set @strSQL = 'select count(*) as Total from [' + @tblName + ']'    
    end
--以上程式碼的意思是如果@doCount傳遞過來的不是0，就執行總數統計。以下的所有程式碼都是@doCount為0的情況
else
    begin
        if @OrderType = 0
            begin
                    set @strOrder ='SELECT ROW_NUMBER() OVER (ORDER BY '+@fldName +' asc) AS RowNumber, '+@strGetFields+' FROM ' 
            end
        else if @OrderType = 1
            begin
                 set @strOrder ='SELECT ROW_NUMBER() OVER (ORDER BY '+@fldName +' desc) AS RowNumber, '+@strGetFields+' FROM ' 
            end         
        else if @OrderType = 2
            begin
                  set @strOrder ='SELECT ROW_NUMBER() OVER (ORDER BY '+@fldName +') AS RowNumber, '+@strGetFields+' FROM ' 
            end
        if @strWhere != ''  
            set @strSQL = ' SELECT TOP ' + str(@PageSize) +' * FROM ( '+@strOrder+' '+@tblName+' where '+@strWhere+' ) t '
            +'WHERE RowNumber >= 1+'+str(@PageSize*(@PageIndex-1))
            else
            set @strSQL = ' SELECT TOP ' + str(@PageSize) +' * FROM ( '+@strOrder+' '+@tblName+' ) t '
            +'WHERE RowNumber >= 1+'+str(@PageSize*(@PageIndex-1))
          end  
print(@strSQL)
exec (@strSQL)

GO