谁有sql 2000的分页存储过程和使用方法啊??
谁有sql 2000的分页存储过程和使用方法啊?? --------------------编程问答--------------------参考:
--------------------编程问答--------------------
------------------------------------------------------------
--作者:灵雨飘零
--时间:2009-02-18
-------------------------------------------------------------
--存储过程的功能:对表 TSys_NoticeInfo 获取页数。
-------------------------------------------------------------
--参数说明:
-------------------------------------------------------------
/*
@Con varchar(2000), --条件
@PageSize int --每页多少条记录
*/
CREATE PROCEDURE USP_TSys_NoticeInfoGetTotalPageCount
@Con varchar(2000), --条件
@PageSize int --每页多少条记录
AS
--计算总页数
Create Table #T(TotalPage int)
Declare @sPageSize varchar(20), @TotalPageCount int
Set @sPageSize=Convert(varchar(20), @PageSize)
Exec('Insert #T(TotalPage) Select Ceiling(Count(*)*1.0/'+@sPageSize+') From TSys_NoticeInfo Where 1=1 '+@Con)
Select @TotalPageCount=TotalPage From #T
Drop Table #T
return @TotalPageCount
GO
收藏了 --------------------编程问答--------------------
没有用过分页存储过程,都不知道怎么用,哎~ --------------------编程问答-------------------- 给你个曾经项目中用过的存储过程
不过排序只能按主键排序
否则会出问题
--------------------编程问答-------------------- 谢谢大家的帮忙 --------------------编程问答-------------------- 都没怎么用过2000了
USE [wis0299]
GO
/****** 对象: StoredProcedure [dbo].[UP_GetRecordByPageOrder] 脚本日期: 03/11/2010 16:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UP_GetRecordByPageOrder]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 显示字段名
@OrderfldName varchar(255), -- 排序字段名
@StatfldName varchar(255), -- 统计字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(4000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量(查询条件过长时可能会出错,可修改100为1000)
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @OrderfldName +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @OrderfldName +' asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from '
+ @tblName + ' where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from ' + @tblName + '' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] 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) + ' ' + @fldName + ' from '
+ @tblName + '' + @strTmp + ' ' + @strOrder
end
if @IsReCount != 0
set @strSQL = @strSQL+' select count(1) as Total from ' + @tblName + ''
if @strWhere!=''
set @strSQL = @strSQL+' where ' + @strWhere
print @strSQL
exec (@strSQL)
--DEMO:
USE [wis0299]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[UP_GetRecordByPageOrder]
@tblName = N'vw_QuerySanitary',
@fldName = N'*',
@OrderfldName = N'UpdateTime',
@StatfldName = N'*',
@PageSize = 10,
@PageIndex = 3,
@IsReCount = 1,
@OrderType = 1,
@strWhere = N'1=1'
SELECT 'Return Value' = @return_value
GO
select top 10 * from [vw_QuerySanitary] where [UpdateTime]<(select min([UpdateTime]) from (select top 20 [UpdateTime] from vw_QuerySanitary where 1=1 order by UpdateTime desc) as tblTmp) and 1=1 order by UpdateTime desc select count(1) as Total from vw_QuerySanitary where 1=1
补充:.NET技术 , ASP.NET