CREATE PROC sp_PageView @sql ntext, --要执行的sql语句 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小 @PageCount int OUTPUT --总页数 AS SET NOCOUNT ON DECLARE @p1 int --初始化分页游标 EXEC sp_cursoropen @cursor=@p1 OUTPUT, @stmt=@sql, @scrollopt=1, @ccopt=1, @rowcount=@PageCount OUTPUT--计算总页数 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 SET @PageCount=(@PageCount+@PageSize-1)/@PageSize IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount SET @PageCurrent=1 ELSE SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据 EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标 EXEC sp_cursorclose @p1这个最简单,按参数传就行了,前台语言配合, 比如前台要记住当前是第几页
--drop procedure p_page --gocreate procedure p_page( @Tables varchar(1000), --表名如testtable @PrimaryKey varchar(100),--表的主键,必须唯一性 @Sort varchar(200) = NULL,--排序字段如f_Name asc或f_name desc(注意只能有一个排序字段) @CurrentPage int = 1,--当前页 @PageSize int = 10,---每页大小 @Fields varchar(1000) = '*',--显示的字段列表 @Filter varchar(1000) = NULL,--条件语句,不加where,如 f_id>3 @Group varchar(1000) = NULL,--分组字段 @TotalPage int output --返回总页数 )WITH ENCRYPTION ---加密存储AS SET NOCOUNT ON Declare @intResult Int Begin Tran DECLARE @sql nvarchar(4000)
if @Filter is null or @Filter='' set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables else set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables + ' where + ' + @Filter EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数 select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数
IF @Sort IS NULL or @Sort = '' SET @Sort = @PrimaryKey DECLARE @SortTable varchar(100) DECLARE @SortName varchar(100) DECLARE @strSortColumn varchar(200) DECLARE @operator char(2) DECLARE @type varchar(100) DECLARE @prec int
IF CHARINDEX('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '') SET @operator = '<=' END ELSE IF CHARINDEX('ASC', @Sort) > 0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'ASC', '') SET @operator = '>=' END
ELSE BEGIN SET @strSortColumn = @SORT SET @operator = '>=' END
IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn)) SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn END
Select @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype Where o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
SET @strPageSize = CAST(@PageSize AS varchar(50)) SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' Where ' + @Filter + ' ' SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' END
IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' + @Group + ' ' ELSE SET @strGroup = ''
set @sql = 'DECLARE @SortColumn ' + @type + ' SET ROWCOUNT ' + @strStartRow + ' Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + ' SET ROWCOUNT ' + @strPageSize + ' Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + ' '
exec(@sql) --print @sql------------------------------------------------------------------------------------------------- If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @intResult ---返回记录总数 EndGO --------------------------------------------------------------------------------------------------------- /// <summary> /// 绑定数据 /// </summary> protected void cfBindData(string sqlwhere) { //分页开始 SqlParameter[] myParms = new SqlParameter[10]; myParms[0] = new SqlParameter("@Tables", SqlDbType.VarChar, 50); myParms[0].Value = "cf_Product"; myParms[1] = new SqlParameter("@PrimaryKey", SqlDbType.VarChar, 50); myParms[1].Value = "cf_id"; myParms[2] = new SqlParameter("@Sort", SqlDbType.VarChar, 50); myParms[2].Value = "cf_id desc"; myParms[3] = new SqlParameter("@CurrentPage", SqlDbType.Int, 4); myParms[3].Value = Convert.ToInt16(pageIndex); myParms[4] = new SqlParameter("@pageSize", SqlDbType.Int, 4); myParms[4].Value = pageSize; myParms[5] = new SqlParameter("@Fields", SqlDbType.VarChar, 500); myParms[5].Value = "* "; myParms[6] = new SqlParameter("@Filter", SqlDbType.VarChar, 500); myParms[6].Value = sqlwhere; myParms[7] = new SqlParameter("@Group", SqlDbType.VarChar, 50); myParms[7].Value = ""; myParms[8] = new SqlParameter("@totalPage", SqlDbType.Int, 4); myParms[8].Direction = ParameterDirection.Output; myParms[9] = new SqlParameter("@totalRecord", SqlDbType.Int, 4); myParms[9].Direction = ParameterDirection.ReturnValue; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ToString()); conn.Open(); SqlCommand sc = new SqlCommand("up_page", conn); sc.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in myParms) { sc.Parameters.Add(parameter); } sc.ExecuteNonQuery(); totalPage = Convert.ToInt16(myParms[8].Value); totalRecord = Convert.ToInt16(myParms[9].Value); SqlDataReader sdr = sc.ExecuteReader(); GV1.DataSourceID = null; GV1.DataSource = sdr; GV1.DataBind(); sc.Dispose(); conn.Close(); conn.Dispose(); //分页结束 }
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOALTER procedure up_PageCut @tblName varchar(1000), -- 表名 @strJoin varchar(3000), -- 连接表 @strGetFields varchar(2000) = '*', -- 需要返回的列 @fldName varchar(200)='', -- 排序的字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(2000) = '' ,-- 查询条件 (注意: 不要加 where) @Counts int out -- 返回记录总数, 非 0 值则返回 这里作运行后返回的记录总数分页用 AS set nocount on declare @strSQL varchar(8000) -- 主语句 declare @CountSQL nVarchar(4000) declare @strTmp varchar(110) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by ' + @fldName +' desc' --如果@OrderType不是0,就执行降序,这句很重要! end else begin set @strTmp = '>(select max' set @strOrder = ' order by ' + @fldName +' asc' end if @PageIndex = 1 begin if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ' + @tblName + ' ' + @strJoin + ' where ' + @strWhere + ' ' + @strOrder else set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from '+ @tblName + ' ' + @strJoin + ' ' + @strOrder --如果是第一页就执行以上代码,这样会加快执行速度 end else begin --以下代码赋予了@strSQL以真正执行的SQL代码 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ' + @tblName + ' ' + @strJoin + ' where ' + @fldName + ' ' + @strTmp + '('+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + ' ' + @strOrder + ') as tblTmp)'+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' ' + ' '+ @strGetFields + ' from ' + @tblName + ' ' + @strJoin + ' where ' + @fldName + ' ' + @strTmp + '(' + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @strWhere + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder end if @strWhere != '' set @CountSQL='select @Counts=count(*) from ' + @tblName + ' where ' + @strWhere +'' else set @CountSQL='select @Counts=count(*) from ' + @tblNameexec sp_executesql @CountSQL, N'@Counts int out ',@Counts out exec ( @strSQL) set nocount off GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
@sql ntext, --要执行的sql语句
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize--关闭分页游标
EXEC sp_cursorclose @p1这个最简单,按参数传就行了,前台语言配合,
比如前台要记住当前是第几页
--gocreate procedure p_page(
@Tables varchar(1000), --表名如testtable
@PrimaryKey varchar(100),--表的主键,必须唯一性
@Sort varchar(200) = NULL,--排序字段如f_Name asc或f_name desc(注意只能有一个排序字段)
@CurrentPage int = 1,--当前页
@PageSize int = 10,---每页大小
@Fields varchar(1000) = '*',--显示的字段列表
@Filter varchar(1000) = NULL,--条件语句,不加where,如 f_id>3
@Group varchar(1000) = NULL,--分组字段
@TotalPage int output --返回总页数
)WITH ENCRYPTION ---加密存储AS SET NOCOUNT ON
Declare @intResult Int Begin Tran DECLARE @sql nvarchar(4000)
if @Filter is null or @Filter=''
set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables
else
set @Sql = 'select @intResult = count(' + @PrimaryKey + ') from ' + @Tables + ' where + ' + @Filter EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--计算总记录数
select @TotalPage=CEILING((@intResult+0.0)/@PageSize)--计算总页数
IF @Sort IS NULL or @Sort = ''
SET @Sort = @PrimaryKey DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
IF CHARINDEX('DESC',@Sort)>0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<='
END
ELSE
IF CHARINDEX('ASC', @Sort) > 0
BEGIN
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>='
END
ELSE
BEGIN
SET @strSortColumn = @SORT
SET @operator = '>='
END
IF CHARINDEX('.', @strSortColumn) > 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END
Select @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
Where o.name = @SortTable AND c.name = @SortName
IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)
IF @CurrentPage < 1
SET @CurrentPage = 1
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' Where ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilter = ''
END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
set @sql = 'DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
'
exec(@sql)
--print @sql------------------------------------------------------------------------------------------------- If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @intResult ---返回记录总数
EndGO
---------------------------------------------------------------------------------------------------------
/// <summary>
/// 绑定数据
/// </summary>
protected void cfBindData(string sqlwhere)
{ //分页开始
SqlParameter[] myParms = new SqlParameter[10]; myParms[0] = new SqlParameter("@Tables", SqlDbType.VarChar, 50);
myParms[0].Value = "cf_Product"; myParms[1] = new SqlParameter("@PrimaryKey", SqlDbType.VarChar, 50);
myParms[1].Value = "cf_id"; myParms[2] = new SqlParameter("@Sort", SqlDbType.VarChar, 50);
myParms[2].Value = "cf_id desc"; myParms[3] = new SqlParameter("@CurrentPage", SqlDbType.Int, 4);
myParms[3].Value = Convert.ToInt16(pageIndex); myParms[4] = new SqlParameter("@pageSize", SqlDbType.Int, 4);
myParms[4].Value = pageSize; myParms[5] = new SqlParameter("@Fields", SqlDbType.VarChar, 500);
myParms[5].Value = "* "; myParms[6] = new SqlParameter("@Filter", SqlDbType.VarChar, 500);
myParms[6].Value = sqlwhere; myParms[7] = new SqlParameter("@Group", SqlDbType.VarChar, 50);
myParms[7].Value = ""; myParms[8] = new SqlParameter("@totalPage", SqlDbType.Int, 4);
myParms[8].Direction = ParameterDirection.Output; myParms[9] = new SqlParameter("@totalRecord", SqlDbType.Int, 4);
myParms[9].Direction = ParameterDirection.ReturnValue; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ToString());
conn.Open();
SqlCommand sc = new SqlCommand("up_page", conn);
sc.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in myParms)
{
sc.Parameters.Add(parameter);
}
sc.ExecuteNonQuery(); totalPage = Convert.ToInt16(myParms[8].Value);
totalRecord = Convert.ToInt16(myParms[9].Value); SqlDataReader sdr = sc.ExecuteReader();
GV1.DataSourceID = null;
GV1.DataSource = sdr;
GV1.DataBind(); sc.Dispose();
conn.Close();
conn.Dispose();
//分页结束
}
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER procedure up_PageCut
@tblName varchar(1000), -- 表名
@strJoin varchar(3000), -- 连接表
@strGetFields varchar(2000) = '*', -- 需要返回的列
@fldName varchar(200)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '' ,-- 查询条件 (注意: 不要加 where)
@Counts int out -- 返回记录总数, 非 0 值则返回 这里作运行后返回的记录总数分页用
AS
set nocount on
declare @strSQL varchar(8000) -- 主语句
declare @CountSQL nVarchar(4000)
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型 if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ' + @tblName + ' ' + @strJoin + ' where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from '+ @tblName + ' ' + @strJoin + ' ' + @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from ' + @tblName + ' ' + @strJoin + ' where ' + @fldName + ' ' + @strTmp + '('+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + ' ' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + ' '+ @strGetFields + ' from ' + @tblName + ' ' + @strJoin + ' where ' + @fldName + ' ' + @strTmp + '(' + @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @fldName + ' from ' + @tblName + ' where ' + @strWhere + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end if @strWhere != ''
set @CountSQL='select @Counts=count(*) from ' + @tblName + ' where ' + @strWhere +''
else
set @CountSQL='select @Counts=count(*) from ' + @tblNameexec sp_executesql @CountSQL, N'@Counts int out ',@Counts out
exec ( @strSQL)
set nocount off
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO