if exists(select 1 from sys.objects where name = 'GetProductsCount' and type = 'P')
drop proc GetProductsCount
go
CREATE PROCEDURE GetProductsCount
as
select count(*) from products
go
--1.使用Top
if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
drop proc GetProductsByPage
go
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
declare @sql nvarchar(4000)
set @sql = 'select top ' + Convert(varchar, @PageSize)
+ ' * from products where productid not in (select top ' + Convert(varchar, (@PageNumber - 1) * @PageSize) + ' productid from products)'
exec sp_executesql @sql
go
--exec GetProductsByPage 1, 10
--exec GetProductsByPage 5, 10
--2.使用臨時(shí)表
if exists(select 1 from sys.objects where name = 'GetProductsByPage' and type = 'P')
drop proc GetProductsByPage
go
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
-- 創(chuàng)建臨時(shí)表
CREATE TABLE #TempProducts
(
ID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName varchar(40) ,
SupplierID int,
CategoryID int,
QuantityPerUnit nvarchar(20),
UnitPrice money,
UnitsInStock smallint,
UnitsOnOrder smallint,
ReorderLevel smallint,
Discontinued bit
)
-- 填充臨時(shí)表
INSERT INTO #TempProducts
(ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
DECLARE @FromID int
DECLARE @ToID int
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM #TempProducts
WHERE ID >= @FromID AND ID <= @ToID
go
--exec GetProductsByPage 1, 10
--exec GetProductsByPage 5, 10
--3.使用表變量
/*
為要分頁(yè)的數(shù)據(jù)創(chuàng)建一個(gè)table變量,這個(gè)table變量里有一個(gè)作為主健的IDENTITY列.這樣需要分頁(yè)的每條記錄在table變量里就和一個(gè)row index(通過(guò)IDENTITY列)關(guān)聯(lián)起來(lái)了.一旦table變量產(chǎn)生,連接數(shù)據(jù)庫(kù)表的SELECT語(yǔ)句就被執(zhí)行,獲取需要的記錄.SET ROWCOUNT用來(lái)限制放到table變量里的記錄的數(shù)量.
當(dāng)SET ROWCOUNT的值指定為PageNumber * PageSize時(shí),這個(gè)方法的效率取決于被請(qǐng)求的頁(yè)數(shù).對(duì)于比較前面的頁(yè)來(lái)說(shuō)
主站蜘蛛池模板：
青龙|
壤塘县|
伊通|
休宁县|
彩票|
诸城市|
内江市|
高唐县|
金寨县|
中卫市|
柯坪县|
元阳县|
广安市|
灵武市|
德阳市|
偃师市|
瓦房店市|
静海县|
九龙城区|
仁化县|
阳原县|
会同县|
麦盖提县|
宜阳县|
桐梓县|
达日县|
讷河市|
醴陵市|
碌曲县|
任丘市|
昆山市|
芦溪县|
青川县|
邹平县|
克山县|
双柏县|
牙克石市|
大理市|
延庆县|
株洲市|
麻栗坡县|
