ALTER procedure S_CurrentSaleNote
(
@CurrentPage int,
@PageSize int,
@InfoCount int output,
@UserId int
)
as
create table #Sales
(
SalesId int identity(1,1) primary key(SalesId),
SaleId int,
ServerId int,
Quantity int,
Price money,
DeliverTime int,
DeliverMethod nvarchar(20),
UserId int,
Escrowed int,
Expiry int,
SaleStatus int
)
insert into #Sales(SaleId,ServerId,Quantity,Price,DeliverTime,DeliverMethod,UserId,Escrowed,Expiry,SaleStatus) select SaleId,ServerId,Quantity,Price,DeliverTime,DeliverMethod,UserId,Escrowed,Expiry,SaleStatus from S_Sale where SaleStatus = 0 and UserId = @UserId
select @InfoCount = count(SalesId) from #Sales
declare @beginId int
declare @endId int
set @beginId = @CurrentPage*@PageSize-@PageSize+1
set @endId = @CurrentPage * @PageSize
select g.GameName,s.ServerName,sa.Quantity,sa.Price,sa.DeliverMethod,sa.DeliverTime,sa.SaleId
from #Sales as sa inner join S_Server as s
on sa.ServerId = s.ServerId
inner join S_Game as g
on s.GameId = g.GameId
where sa.SalesId between @beginId and @endId
Return
大家帮我优化一下!!thank you ...分不够可以加!
(
@CurrentPage int,
@PageSize int,
@InfoCount int output,
@UserId int
)
as
create table #Sales
(
SalesId int identity(1,1) primary key(SalesId),
SaleId int,
ServerId int,
Quantity int,
Price money,
DeliverTime int,
DeliverMethod nvarchar(20),
UserId int,
Escrowed int,
Expiry int,
SaleStatus int
)
insert into #Sales(SaleId,ServerId,Quantity,Price,DeliverTime,DeliverMethod,UserId,Escrowed,Expiry,SaleStatus) select SaleId,ServerId,Quantity,Price,DeliverTime,DeliverMethod,UserId,Escrowed,Expiry,SaleStatus from S_Sale where SaleStatus = 0 and UserId = @UserId
select @InfoCount = count(SalesId) from #Sales
declare @beginId int
declare @endId int
set @beginId = @CurrentPage*@PageSize-@PageSize+1
set @endId = @CurrentPage * @PageSize
select g.GameName,s.ServerName,sa.Quantity,sa.Price,sa.DeliverMethod,sa.DeliverTime,sa.SaleId
from #Sales as sa inner join S_Server as s
on sa.ServerId = s.ServerId
inner join S_Game as g
on s.GameId = g.GameId
where sa.SalesId between @beginId and @endId
Return
大家帮我优化一下!!thank you ...分不够可以加!
第一个结果集
select SaleId,ServerId,Quantity,Price,DeliverTime,DeliverMethod,UserId,Escrowed,Expiry,SaleStatus from S_Sale where SaleStatus = 0 and UserId = @UserId
select @InfoCount = count(SalesId) from #Sales
declare @beginId int
declare @endId int
set @beginId = @CurrentPage*@PageSize-@PageSize+1
set @endId = @CurrentPage * @PageSize
第二个结果集
select g.GameName,s.ServerName,sa.Quantity,sa.Price,sa.DeliverMethod,sa.DeliverTime,sa.SaleId
from #Sales as sa inner join S_Server as s
on sa.ServerId = s.ServerId
inner join S_Game as g
on s.GameId = g.GameId
where sa.SalesId between @beginId and @endId
你好像返回了两个结果集。
下面是我搜索的分页存储过程
引用别人的:
-- =============================================
-- 檔案名 P_Page
-- Copyright(c) 大理資訊
-- 創建人 limin_he([email protected])
-- 創建日期 2006/09/08
-- 描述 利用SQL查詢語句進行分頁
-- 輸入
-- {
-- @SQL : SQL查詢語句
-- @Order : 排序欄位
-- @CurPage : 當前頁
-- @PageRows : 每頁顯示行數
-- @TotalRecorder: 查詢記錄總數(輸出參數)
-- }
-- 輸出
-- {
-- BookTypeID ,BookTypeID ,BookTypeNameE ,Description ,Sequence ,BookNum ,BookNum ,EditPerson
-- }
-- =============================================
CREATE PROCEDURE [dbo].[P_Page]
-- Add the parameters for the stored procedure here
@SQL Nvarchar(2000),
@Order Nvarchar(20),
@CurPage int,
@PageRows int,
@TotalRecorder int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
declare @ExceSQL nvarchar(4000)
--設置開始行號
declare @start_row_num AS int
SET @start_row_num = (@CurPage - 1) * @PageRows
--設置標識語句
declare @RowNumber nvarchar(100)
set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from ' set @SQL = Replace(@SQL,' from ',@RowNumber) --獲得總記錄數
set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
select @TotalRecorder=max(RowNumber) from tmp' execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output --設置查詢語句
set @ExceSQL = 'WITH tmp AS (' + @SQL + ')
select * from tmp where RowNumber between ' + Convert(nvarchar,@start_row_num)
+ ' And ' + Convert(nvarchar,@start_row_num+@PageRows) execute(@ExceSQL)END
况且用临时表来分页,好像性能上不太好。
说错勿怪,修改不好勿怪!
修改代码如下:
sql2005.
ALTER procedure S_CurrentSaleNote
(
@startIndex int, --当前所在页的索引号
@PageSize int,
@UserId int
)
asWITH salesTmp as
(
select ROW_NUMBER() OVER(ORDER BY S_Sale.SaleId DESC) AS ROW,g.GameName,s.ServerName,sa.Quantity,sa.Price,sa.DeliverMethod,sa.DeliverTime,sa.SaleId
from S_Sale as sa inner join S_Server as s
on sa.ServerId = s.ServerId
inner join S_Game as g
on s.GameId = g.GameId
where sa.SaleStatus = 0 and sa.UserId = @UserId
)
SELECT * FROM salesTmp where ROW BETWEEN @startIndex and @startIndex+@pageSize-1Return
xray2005(风车车--要飞翔,必须靠自己!) (
你的分页可以避免id的缺失吗?
例如某行被删除了
不过这个是SQL2005的新特性, SQL 2000 就不行了。