求一个用存储过程分页的实例。最好有表结构,存储过程,以后前后台的代码,最简单的就可以了,比如用个前台用个gridview,绑定一张表。对这个gridview分页
解决方案 »
- 求帮助:怎么查询不重复的记录
- 求助如何在vs.net 2005创建asp.net web应用程序
- 导出gridview的特定列和特定行到excel中?
- 如何删除表employees中所有字段名里面含有"_"字符的字段
- 請問如何用ASP.NET獲取不同網段下主機的MAC地址,高手請進.
- asp.net(vb)小问题不会做
- =====100分,不是很难的问题!在线等!=======
- DataGrid怎样做出这样的效果(前台问题)
- 一个关于函数的问题,vb的,在vb文件中的vb函数好像不能执行
- 下载了treeVIew控件,并按例子写了代码.可树状效果出不来.
- 网页实现winform
- 提交时,显示div锁住页面无效
里面就有存储过程的demo
大家互相轉載
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[PAKS_sp_GetRecordByPageOrder] @tblName varchar(255),
@fldName varchar(255),
@OrderfldName varchar(255),
@PageSize int = 10,
@PageIndex int = 1,
@strWhere varchar(1000) = ''
AS
declare @strSQL varchar(6000)
declare @strTmp varchar(1000)
declare @strOrder varchar(400) if @strWhere = '' set @strWhere='1=1'
if @OrderfldName=''
begin
set @strOrder = ' order by '+ @fldName +' desc'
end
else
begin
set @strOrder = ' order by '+@OrderfldName
end
if @PageIndex = 1
begin
set @strSQL = 'select top ' + str(@PageSize) + ' * from '
+ @tblName+' where ' + @strWhere + @strOrder;
--print @strSQL
end
else
begin
set @strSQL = 'select top ' + str(@PageSize) + ' * from '
+ @tblName + ' where [' +@fldName+ '] not in '
+ '(select top ' + str((@PageIndex - 1)*@PageSize) + ' '
+ @fldName +' from ' + @tblName + ' where ' + @strWhere + @strOrder + ')'
+ ' and ' + @strWhere + @strOrder;
--print @strSQL
end
exec (@strSQL)
set QUOTED_IDENTIFIER ON
go--用途:分页存储过程(对有主键的表效率极高)
--说明:
------------------------------------
ALTER PROCEDURE [dbo].[PAKS_sp_GetRecordByPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 0 值降序,非 0 值则升序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
declare @strOrder varchar(400) -- 排序类型
if @IsReCount = 0
begin
if @OrderType = 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] 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) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
--print @strSQL
end
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
if @strWhere!=''
set @strSQL = @strSQL+' where ' + @strWhere
--print @strSQL
end
exec (@strSQL)
去下载吧