分页存储过程（SQL SERVER）

适用系统：魔方OA/CRM/ERP/MES/HIS/HR/CMS/SHOP等

官网网址：http://www.mojocube.com/

文档网址：http://www.mojocube.com/doc/default.aspx

演示地址：http://www.mojocube.com/source.html

pagination3

分页存储过程

列名	类型	是否必填	描述
tblName	varchar(50)	是	表名
strGetFields	varchar(5000)	是	字段名(全部字段为*)
fldName	varchar(5000)	是	排序字段(必须!支持多字段)
strWhere	varchar(5000)	否	条件语句(不用加where)
pageSize	int	是	每页多少条记录
pageIndex	int	是	指定当前为第几页
OrderType	bit	是	设置排序类型, 非0值则降序
doCount	bit	是	计算总记录数

USE [HR]
GO
/****** Object:  StoredProcedure [dbo].[pagination3]    Script Date: 2023-10-08 14:33:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pagination3]  --新建存储过程用:CREATE PROCEDURE [dbo].[pagination3]
@tblName varchar(50),         --表名
@strGetFields varchar(5000) = '*',     --字段名(全部字段为*)
@fldName varchar(5000),         --排序字段(必须!支持多字段)
@strWhere varchar(5000) = Null,--条件语句(不用加where)
@pageSize int,                     --每页多少条记录
@pageIndex int = 1 ,             --指定当前为第几页
@OrderType bit=0,           -- 设置排序类型, 非 0 值则降序 
@doCount bit = 0 
as
begin
     Declare @sql nvarchar(4000)    
     --计算总记录数
     if @doCount != 0
		begin
			if (@strWhere='' or @strWhere=NULL)
				set @sql = 'select count(*) as Total from [' + @tblName + ']'
			else
				set @sql = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere
		end      

	else
		begin   
			 if (@strWhere='' or @strWhere=NULL)
				if(@OrderType=1)
					set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @fldName + ' desc) as rowId,' + @strGetFields + ' from ' + @tblName 
				else
					set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @fldName + ' asc) as rowId,' + @strGetFields + ' from ' + @tblName 
			 else
				 if(@OrderType=1)
					set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @fldName + ' desc) as rowId,' + @strGetFields + ' from ' + @tblName + ' where ' + @strWhere    
				else
					set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @fldName + ' asc) as rowId,' + @strGetFields + ' from ' + @tblName + ' where ' + @strWhere   

			  --处理开始点和结束点
			 Declare @StartRecord int
			 Declare @EndRecord int 
		    
			 set @StartRecord = (@pageIndex-1)*@PageSize + 1
			 set @EndRecord = @StartRecord + @pageSize - 1

			 --继续合成sql语句
			if(@OrderType=1)
				set @Sql = @Sql + ') as ' + @tblName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +   Convert(varchar(50),@EndRecord) + 'order  by '+@fldName+' desc'
			else
				set @Sql = @Sql + ') as ' + @tblName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +   Convert(varchar(50),@EndRecord) + 'order  by '+@fldName+' asc'
		    
		end
end
Exec(@sql)