微软网站分页的例子CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC-- Return total count
SELECT COUNT(OrderID) FROM Orders-- Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexIDEND
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC-- Return total count
SELECT COUNT(OrderID) FROM Orders-- Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexIDEND
解决方案 »
- 如何用VB代码实现在这个VB应用程序第一次运行时利用数据库的备份创建当前数据库。
- 字符串构造问题
- N在 WHERE object_id = OBJECT_ID(N'[dbo].[]')的作用是什么,求高手解答
- win7 64位,在ODBC的DNS中对sql server配置成功,但连接失败
- 远程连接数据库用方式好
- MYSQL connector/ODBC中出现的问题
- SQL查询艺术(T-SQL)
- 我的机器是Win2000专业版,现在想装SQL2000,能注册本地的实例,并且在本地建数据库,应该装哪一个版本的啊??谢谢!急!
- 并发控制中封锁问题!
- 高难度:在存储过程中进行分布式查询、更新、插入、删除等操作的问题(高手请进)
- 一个统计报表的问题,有点难度,请高手解决
- 小问题,请大家帮忙!
----------------------------------------------------------------------------------
create table T(ID int,name varchar(100))
insert into T select 1,'A'
insert into T select 2,'B'
insert into T select 3,'C'
insert into T select 4,'D'
insert into T select 5,'E'
insert into T select 6,'F'
insert into T select 7,'G'
insert into T select 8,'H'
insert into T select 9,'I'
insert into T select 10,'J'
insert into T select 11,'K'
insert into T select 12,'L'
GOcreate procedure APSYS_PAGINATION
@sqlstr nvarchar(4000), --查询字符串
@sqltmp nvarchar(4000), --生成与查询结果集字段集数据类型一致的临时表的SQL字符串
@pageindex int, --第N页(当前页码)
@pagesize int, --每页行数
@pagecount int output, --总页数,输出参数
@rowcount int output --总行数,输出参数
as
begin
set nocount on
declare @P1 int --P1是游标的id
declare @var nvarchar(4000)
set @var = N' create table #T' + @sqltmp +
N' insert #T exec sp_cursoropen @P1 output, @sqlstr,1, 1, @rowcount = @rowcount output'
exec sp_executesql @var,
N'@sqlstr nvarchar(4000),@P1 int output,@rowcount int output',
@sqlstr,@P1 output,@rowcount output
set @pagecount = ceiling(1.0 * @rowcount / @pagesize)
set @pageindex = (@pageindex - 1) * @pagesize + 1
exec sp_cursorfetch @P1, 16, @pageindex, @pagesize
exec sp_cursorclose @P1
set nocount off
end
GOEXEC APSYS_PAGINATION N'select * from T','(id int,name varchar(100))',2,2,1,1drop procedure APSYS_PAGINATION
drop table T