USE [Tgbus_WOW]
GO
/****** 对象: StoredProcedure [dbo].[Npc_Get] 脚本日期: 05/20/2008 09:46:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Npc_Get]
(
@PageIndex int,
@PageSize int,
@OrderBy int
)
AS
IF @PageSize = 0
Select * From Npc
Else IF @PageSize > 0
BEGIN
Declare @PageLowerBound int,
@PageUpperBound int,
@PageCount int,
@NpcCount int
--临时索引表--
Create Table #PageIndex
(
IndexID int IDENTITY(1,1) NOT NULL,
NpcID int
)
IF @OrderBy = 1
Begin
Insert Into #PageIndex(NpcID)
Select
NpcID
From
Npc
Order By
[Level] DESC
End
Else IF @OrderBy = 2
Begin
Insert Into #PageIndex(NpcID)
Select
NpcID
From
Npc
Order By
NpcTypeID DESC
End
Else IF @OrderBy = 3
Begin
Insert Into #PageIndex(NpcID)
Select
NpcID
From
Npc
Order By
AreaID DESC
End
--最大页码计算--
Set @NpcCount = @@ROWCOUNT
Set @PageCount = (@NpcCount - 1) / @PageSize + 1
--页码范围校验--
IF @PageIndex < 1 Set @PageIndex = 1
IF @PageIndex >=@PageCount Set @PageIndex = @PageCount
Set @PageLowerBound = @PageSize * (@PageIndex - 1)
Set @PageUpperBound = @PageLowerBound + @PageSize + 1
--返回记录--
Select
tS.*,
nt.NpcTypeName,
nl.NpcLevelName,
nc.NpcClassName,
a.AreaName,
c.CampName
From
Npc tS
Left Join NpcType nt on tS.NpcTypeID = nt.NpcTypeID
Left Join NpcLevel nl on tS.NpcLevelID = nl.NpcLevelID
Left Join NpcClass nc on tS.NpcClassID = nc.NpcClassID
Left Join Area a on tS.AreaID = a.AreaID
Left Join Camp c on tS.CampID = c.CampID,
#PageIndex tPI
Where
tS.NpcID = tPI.NpcID AND
tPI.IndexID > @PageLowerBound AND
tPI.IndexID < @PageUpperBound
Order By
IndexID
Select @NpcCount AS NpcCount,@PageCount AS PageCount,@@ROWCOUNT AS ReturnCount
END
