分页存储过程 http://blog.csdn.net/zhoufoxcn

http://www.w3school.com.cn

USE [AManage]
GO

/****** Object:  StoredProcedure [dbo].[UP_GetRecordByPage_FL]    Script Date: 06/30/2013 16:31:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UP_GetRecordByPage_FL]
@TblName varchar(max), --表名、视图名、查询语句
@PageSize int=10,   --每页的大小(行数)
@PageIndex int=1,   --要显示的页
@FldList VarChar(max), --要显示的字段列表(为空列出所有字段）
@FldOrder NVarChar(max), --排序字段列表
@StrWhere VarChar(max),
@FdOrderDirec int, --设置排序顺序；0：升序；1：降序
@Rows int  OUTPUT      -- 输出记录数, 如果@rows为null, 则输出记录数, 否则不要输出
AS
declare @strSQL varchar(max) -- 主语句
declare @strTmp varchar(max) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @head VarChar(max)
declare @Sql varchar(max)
if (@FldList ='')
set @FldList = '*'
if (@FdOrderDirec=0)
set @strOrder = ' Order by ' + @FldOrder + ' asc'
else
set @strOrder = ' Order by ' + @FldOrder + ' desc'
SET @TblName=@TblName+' '+@StrWhere
declare @lbuseidentity nvarchar(max)
set @lbuseidentity = 'select @rows = count(*) from '+@TblName
exec sp_executesql @lbuseidentity, N'@Rows int out', @Rows out
declare @StartNums varchar(20)
declare @EndNums varchar(20)
set @StartNums = (@PageSize*(@PageIndex-1)) + 1
set @EndNums = (@PageSize*@PageIndex)
set @Sql = '
with t_rowtable as ( select row_number() over(' + @strOrder + ' ) as row_number,' + @FldList + ' from ' + @TblName + ' )
select ' + @FldList + ' from t_rowtable where row_number>=' + @StartNums + ' and row_number<=' + @EndNums + @strOrder
exec (@Sql)
GO

 

USE [Huacisoft.Crm.Dev]
GO

/****** Object:  StoredProcedure [dbo].[P_CommonPagingList]    Script Date: 01/16/2014 00:00:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  liangweiqing
-- Create date: 2013-12-15
-- Description: 公用分页存储过程
-- =============================================
CREATE PROCEDURE [dbo].[P_CommonPagingList]
@TblName VARCHAR(MAX), --表名、视图名
@PageSize int=10,   --每页的大小(行数)
@PageIndex int=1,   --要显示的页
@FldList VARCHAR(MAX), --要显示的字段列表(为空或*列出所有字段）
@FldOrder NVARCHAR(MAX), --排序字段列表
@StrWhere VARCHAR(MAX),--过滤条件
@FdOrderDirec INT, --设置排序顺序；0：升序；1：降序
@Rows INT OUTPUT      -- 输出记录数, 如果@rows为null, 则输出记录数, 否则不要输出
AS
DECLARE @strOrder VARCHAR(400) -- 排序类型
DECLARE @Sql VARCHAR(MAX)
IF (@FldList ='')
SET @FldList = '*'
IF (@FdOrderDirec=0)
SET @strOrder = ' Order by ' + @FldOrder + ' asc'
ELSE
SET @strOrder = ' Order by ' + @FldOrder + ' desc'
SET @TblName=@TblName+' '+@StrWhere
DECLARE @lbuseidentity NVARCHAR(MAX)
SET @lbuseidentity = 'select @rows = count(*) from  '+@TblName
EXEC sp_executesql @lbuseidentity, N'@Rows int out', @Rows out
DECLARE @StartNums VARCHAR(20)
DECLARE @EndNums VARCHAR(20)
SET @StartNums = (@PageSize*(@PageIndex-1)) + 1
SET @EndNums = (@PageSize*@PageIndex)
SET @Sql = '
with t_rowtable as ( select row_number() over(' + @strOrder + ' ) as row_number,' + @FldList + ' from ' + @TblName + ' )
select ' + @FldList + ' from t_rowtable where row_number>=' + @StartNums + ' and row_number<=' + @EndNums + @strOrder
EXEC (@Sql)

GO

 

USE [Huacisoft.Crm.Dev]
GO

/****** Object:  StoredProcedure [dbo].[P_CommonPagingList]    Script Date: 01/16/2014 00:00:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  liangweiqing
-- Create date: 2013-12-15
-- Description: 公用分页存储过程
-- =============================================
CREATE PROCEDURE [dbo].[P_CommonPagingList]
@TblName VARCHAR(MAX), --表名、视图名
@PageSize int=10,   --每页的大小(行数)
@PageIndex int=1,   --要显示的页
@FldList VARCHAR(MAX), --要显示的字段列表(为空或*列出所有字段）
@FldOrder NVARCHAR(MAX), --排序字段列表
@StrWhere VARCHAR(MAX),--过滤条件
@FdOrderDirec INT, --设置排序顺序；0：升序；1：降序
@Rows INT OUTPUT      -- 输出记录数, 如果@rows为null, 则输出记录数, 否则不要输出
AS
DECLARE @strOrder VARCHAR(400) -- 排序类型
DECLARE @Sql VARCHAR(MAX)
IF (@FldList ='')
SET @FldList = '*'
IF (@FdOrderDirec=0)
SET @strOrder = ' Order by ' + @FldOrder + ' asc'
ELSE
SET @strOrder = ' Order by ' + @FldOrder + ' desc'
SET @TblName=@TblName+' '+@StrWhere
DECLARE @lbuseidentity NVARCHAR(MAX)
SET @lbuseidentity = 'select @rows = count(*) from  '+@TblName
EXEC sp_executesql @lbuseidentity, N'@Rows int out', @Rows out
DECLARE @StartNums VARCHAR(20)
DECLARE @EndNums VARCHAR(20)
SET @StartNums = (@PageSize*(@PageIndex-1)) + 1
SET @EndNums = (@PageSize*@PageIndex)
SET @Sql = '
with t_rowtable as ( select row_number() over(' + @strOrder + ' ) as row_number,' + @FldList + ' from ' + @TblName + ' )
select ' + @FldList + ' from t_rowtable where row_number>=' + @StartNums + ' and row_number<=' + @EndNums + @strOrder
EXEC (@Sql)

GO