CREATE PROCEDURE spChangeEquItemInstance
(
@IUID1 bigint, @SlotID1 smallint, @UseCharPlayTime1 int, @AccumuUsageTime1 int,
@IUID2 bigint, @SlotID2 smallint, @UseCharPlayTime2 int, @AccumuUsageTime2 int,
@IUID3 bigint, @SlotID3 smallint, @UseCharPlayTime3 int, @AccumuUsageTime3 int
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RowCount int
BEGIN TRAN
-- 1
UPDATE ItemInstance SET SlotID=@SlotID1, UseCharPlayTime=@UseCharPlayTime1,
AccumuUsageTime=@AccumuUsageTime1 WHERE IUID=@IUID1; SET @RowCount = @@ROWCOUNT
IF( @RowCount < 1 OR @@ERROR <> 0 )
BEGIN
GOTO ERROR_HANDLER;
END
-- 2
IF( @IUID2 <> 0 )
BEGIN
UPDATE ItemInstance SET SlotID=@SlotID2, UseCharPlayTime=@UseCharPlayTime2,
AccumuUsageTime=@AccumuUsageTime2 WHERE IUID=@IUID2; SET @RowCount = @@ROWCOUNT
IF( @RowCount < 1 OR @@ERROR <> 0 )
BEGIN
GOTO ERROR_HANDLER;
END
END
-- 3
IF( @IUID3 <> 0 )
BEGIN
UPDATE ItemInstance SET SlotID=@SlotID3, UseCharPlayTime=@UseCharPlayTime3,
AccumuUsageTime=@AccumuUsageTime3 WHERE IUID=@IUID3; SET @RowCount = @@ROWCOUNT
IF( @RowCount < 1 OR @@ERROR <> 0 )
BEGIN
GOTO ERROR_HANDLER;
END
END
COMMIT TRAN
return;
ERROR_HANDLER:
IF @@TRANCOUNT <> 0 ROLLBACK TRAN
RAISERROR(N'Failed Update IUID1(%d), IUID2(%d), IUID3(%d).'
, 11
, 1
, @IUID1, @IUID2, @IUID3);
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spCharBattleRecordUpdate
(
@Update VARCHAR(2000)
, @Insert VARCHAR(2000)
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
UPDATE cbr
SET cbr.Value = cbr.Value + C.Value
FROM (SELECT B.CID
, CAST(SUBSTRING(B.Val, 1, CHARINDEX('.', B.Val) - 1) AS SMALLINT) Kind
, CAST(SUBSTRING(B.Val + '.', CHARINDEX('.', B.Val) + 1, LEN(B.Val) - CHARINDEX('.', B.Val)) AS INT) Value
FROM (SELECT CAST(SUBSTRING(A.val_str, 1, CHARINDEX('.', A.val_str) - 1) AS INT) CID
, SUBSTRING(A.val_str + '.', CHARINDEX('.', A.val_str) + 1, LEN(val_str) - CHARINDEX('.', A.val_str)) Val
FROM (SELECT LTRIM(RTRIM(SUBSTRING(@Update, c.num, CHARINDEX(',', @Update + ',', c.num) - c.num))) AS val_str
FROM dbo.cpt c
WHERE c.num <= LEN(@Update)
AND SUBSTRING(',' + @Update, c.num, 1) = ',') A) B) C
, dbo.CharBattleRecord cbr
WHERE cbr.CID = C.CID AND cbr.Type = C.Type;
IF (0 <> @@ERROR)
BEGIN
ROLLBACK TRAN;
RAISERROR(N'ERROR BATTLE UPDATE.', 11, 1);
END
INSERT INTO dbo.CharBattleRecord(CID, Type, Value)
SELECT B.CID
, CAST(SUBSTRING(B.Val, 1, CHARINDEX('.', B.Val) - 1) AS SMALLINT) Type
, CAST(SUBSTRING(B.Val + '.', CHARINDEX('.', B.Val) + 1, LEN(B.Val) - CHARINDEX('.', B.Val)) AS INT) Value
FROM (SELECT CAST(SUBSTRING(A.val_str, 1, CHARINDEX('.', A.val_str) - 1) AS INT) CID
, SUBSTRING(A.val_str + '.', CHARINDEX('.', A.val_str) + 1, LEN(val_str) - CHARINDEX('.', A.val_str)) Val
FROM (SELECT LTRIM(RTRIM(SUBSTRING(@Insert, c.num, CHARINDEX(',', @Insert + ',', c.num) - c.num))) AS val_str
FROM dbo.cpt c
WHERE c.num <= LEN(@Insert)
AND SUBSTRING(',' + @Insert, c.num, 1) = ',') A) B;
IF (0 <> @@ERROR)
BEGIN
ROLLBACK TRAN;
RAISERROR(N'ERROR BATTLE INSERT.', 11, 1);
END
COMMIT TRAN;
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spDeleteCharacter
(
@cid int
)
AS
BEGIN
SET NOCOUNT ON;
UPDATE Character SET DeleteFlag = 1 WHERE cid = @cid
IF (0 = @@ROWCOUNT)
BEGIN
RAISERROR(N'invalid character(CID:%d).'
, 11
, 1
, @CID);
END
UPDATE CharacterLook SET DeleteFlag = 1 WHERE CID = @cid
UPDATE ItemInstance SET UsageState = 10 WHERE CID = @cid
DELETE FROM Friend WHERE CID = @cid OR FriendCID = @cid
DELETE FROM ChoiceProduct WHERE CID = @cid
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spDeleteChoiceProduct
(
@ChoiceProdID bigint
)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM ChoiceProduct WHERE ChoiceProdID = @ChoiceProdID
IF (0 = @@ROWCOUNT)
BEGIN
RAISERROR(N'invalid ChoiceProdID(ChoiceProdID:%I64d).'
, 11
, 1
, @ChoiceProdID);
END
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spDeleteFriend
(
@CID int,
@FriendCID int
)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Friend WHERE CID = @CID AND FriendCID = @FriendCID
IF (0 = @@ROWCOUNT)
BEGIN
RAISERROR(N'invalid character(CID:%d), friend(CID:%d).'
, 11
, 1
, @CID, @FriendCID);
END
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spDeleteItemInstance
(
@CID int,
@IUID bigint
)
AS
BEGIN
SET NOCOUNT ON;
UPDATE ItemInstance SET UsageState = 10 WHERE CID = @CID AND IUID = @IUID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spDeletePatchSchedule
(
@PatchID int
)
AS
BEGIN
DELETE FROM ServerPatchSchedule WHERE PatchID = @PatchID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spDevAddItemInstance
(
@ItemId int,
@CID int,
@Durability float,
@SlotType tinyint,
@CurTime int,
@ExpirationType tinyint,
@UsagePeriod int
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @IUID bigint
INSERT INTO ItemInstance (ItemID, CID, Durability, Color, SlotType,
SlotID, UsagePeriod, AddUsagePeriod, CharPlayTime, UseCharPlayTime, AccumuUsageTime,
UsageState, ExpirationType ) VALUES
(@ItemId, @CID, @Durability, 0, @SlotType,
-1, @UsagePeriod, 0, @CurTime, 0, 0,
0, @ExpirationType)
SET @IUID = SCOPE_IDENTITY()
SELECT @IUID AS IUID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertCharacter
(
@AID int,
@Name nvarchar(20),
@SlotIndex smallint,
@Sex tinyint,
@Class tinyint
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FindCID int
SELECT @FindCID = CID FROM Character WHERE Name = @Name AND DeleteFlag = 0
IF (@FindCID is not null)
BEGIN
SELECT CID = -1
return
END
INSERT INTO Character (AID, Name, DeleteFlag, SlotIndex, Sex, Class, Level, AdminLevel, BP, Cash)
VALUES (@AID, @Name, 0, @SlotIndex, @Sex, @Class, 1, 0, 0, 0)
SET @FindCID = SCOPE_IDENTITY()
INSERT INTO CharacterLook (AID, CID) VALUES (@AID, @FindCID)
/*
IF (1 = @Class)
BEGIN
-- 1 Head(4)
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10179002, @FindCID, 0, 0
, 1, 17, 0, 0, '2011-12-25'
, 0, 0, 0, 0, 0)
-- 2 Top(1)
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10129002, @FindCID, 0, 0
, 1, 12, 0, 0, '2011-12-25'
, 0, 0, 0, 0, 0)
-- 3 Pants(2)
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10139002, @FindCID, 0, 0
, 1, 13, 0, 0, '2011-12-25'
, 0, 0, 0, 0, 0)
-- 4 Hand(12)
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10149002, @FindCID, 0, 0
, 1, 14, 0, 0, '2011-12-25'
, 0, 0, 0, 0, 0)
-- 5 Foot(3)
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10159002, @FindCID, 0, 0
, 1, 15, 0, 0, '2011-12-25'
, 0, 0, 0, 0, 0)
END
*/
-- ¾î½Ø½Å
IF (1 = @Class)
BEGIN
-- ¸ÞÀÎ 1
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10400001, @FindCID, 0, 0
, 1, 101, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 2
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10500002, @FindCID, 0, 0
, 1, 102, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¼ºê 1
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10560001, @FindCID, 0, 0
, 1, 201, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 3
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10470001, @FindCID, 0, 0
, 1, 103, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 4
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10420001, @FindCID, 0, 0
, 1, 104, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 5
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10440001, @FindCID, 0, 0
, 1, 105, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
END
-- ÆÄÀÌÅÍ
IF (2 = @Class)
BEGIN
-- ¸ÞÀÎ 1
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10410001, @FindCID, 0, 0
, 1, 101, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 2
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10510001, @FindCID, 0, 0
, 1, 102, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¼ºê 1
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10570001, @FindCID, 0, 0
, 1, 201, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 3
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10430001, @FindCID, 0, 0
, 1, 103, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 4
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10470001, @FindCID, 0, 0
, 1, 104, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 5
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10480001, @FindCID, 0, 0
, 1, 105, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
END
-- Çìºñ
IF (4 = @Class)
BEGIN
-- ¸ÞÀÎ 1
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10450001, @FindCID, 0, 0
, 1, 101, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 2
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10460001, @FindCID, 0, 0
, 1, 102, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 3
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10530001, @FindCID, 0, 0
, 1, 103, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 4
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10550001, @FindCID, 0, 0
, 1, 104, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
-- ¸ÞÀÎ 5
INSERT INTO ItemInstance (ItemID, CID, Durability, Color
,SlotType, SlotID, UsagePeriod, AddUsagePeriod, ExpiDate
,CharPlayTime, UseCharPlayTime, AccumuUsageTime, UsageState, ExpirationType)
VALUES (10470001, @FindCID, 0, 0
, 1, 105, 0, 0, '2012-12-25'
, 0, 0, 0, 0, 0)
END
select @FindCID AS CID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertChoiceProduct
(
@CID int,
@Type tinyint,
@ProdSellPoliID int,
@ProdID int
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ChoiceProdID bigint
INSERT INTO ChoiceProduct (CID, Type, ProdSellPoliID, ProdID)
VALUES (@CID, @Type, @ProdSellPoliID, @ProdID)
SET @ChoiceProdID = SCOPE_IDENTITY()
SELECT @ChoiceProdID AS ChoiceProdID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertFriend
(
@CID int,
@FriendName varchar(20)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FindCID int
SELECT @FindCID = CID FROM Character WHERE Name = @FriendName AND DeleteFlag = 0
IF (@FindCID is null)
BEGIN
SELECT FriendCID = 0
return
END
INSERT INTO Friend (CID, FriendCID) VALUES (@CID, @FindCID)
SELECT FriendCID = @FindCID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertKeeperInfo
(
@ID int,
@IP varchar(24),
@Port int,
@Name varchar(24)
)
AS
BEGIN
INSERT INTO KeeperList(ID, IP, Port, Name) VALUES (@ID, @IP, @Port, @Name)
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertPatchSchedule
(
@KeeperID int,
@ProgramID int,
@PatchTime datetime,
@CycleMinute int
)
AS
BEGIN
IF (@CycleMinute = 0 AND datediff(minute, getdate(), @PatchTime) < 0)
return;
INSERT INTO ServerPatchSchedule(KeeperID, ProgramID, PatchTime, CycleMinute)
VALUES (@KeeperID, @ProgramID, @PatchTime, @CycleMinute)
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertProgramInfo
(
@ProgramID int,
@KeeperID int,
@ProgramName varchar(32)
)
AS
BEGIN
INSERT INTO ProgramList(ProgramID, KeeperID, ProgramName) VALUES (@ProgramID, @KeeperID, @ProgramName)
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertServerPatchLog
(
@ProgramID int,
@PatchResult int
)
AS
BEGIN
INSERT INTO ServerPatchLog(ProgramID, PatchTime, PatchResult) VALUES (@ProgramID, getdate(), @PatchResult)
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertServerStartLog
(
@ProgramID int
)
AS
BEGIN
INSERT INTO ServerOperationLog (ProgramID, SrartTime, EndTime) VALUES (@ProgramID, getdate(), null)
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spInsertServerStopLog
(
@ProgramID int
)
AS
BEGIN
DECLARE @FindOperationID int
SELECT @FindOperationID = max(OperationID) FROM ServerOperationLog WHERE ProgramID = @ProgramID
IF @FindOperationID is not null
BEGIN
UPDATE ServerOperationLog SET EndTime = getdate() WHERE OperationID = @FindOperationID
END
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spLogin
(
@ID nvarchar(20)
)
AS
BEGIN
SELECT AID, PWD FROM Login WHERE id = @ID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSaveCharacterData
(
@CID int, @CharPlayTime int, @BP int, @LatestBattleType tinyint,
@SetItemID int, @SetColor int, @UnderwearItemID int, @UnderwearColor int, @TopItemID int, @TopColor int,
@PantsItemID int, @PantsColor int, @HandItemID int, @HandColor int, @FootItemID int, @FootColor int,
@BackItemID int, @BackColor int, @HeadItemID int, @HeadColor int, @FaceItemID int, @FaceColor int,
@EarringItemID int, @EarringColor int, @NeckItemID int, @NeckColor int, @FingerItemID int, @FingerColor int
)
AS
BEGIN
set nocount on;
DECLARE @RowCount int
BEGIN TRAN
-- 1
UPDATE Character SET CharPlayTime=@CharPlayTime, BP=@BP, LatestBattleType=@LatestBattleType WHERE CID=@CID; SET @RowCount = @@ROWCOUNT
IF( @RowCount < 1 OR @@ERROR <> 0 )
BEGIN
SELECT UpdateRowCount = -1;
GOTO ERROR_HANDLER;
END
-- 2
UPDATE CharacterLook SET SetItemID=@SetItemID, SetColor=@SetColor,
UnderwearItemID=@UnderwearItemID, UnderwearColor=@UnderwearColor,
TopItemID=@TopItemID, TopColor=@TopColor,
PantsItemID=@PantsItemID, PantsColor=@PantsColor,
HandItemID=@HandItemID, HandColor=@HandColor,
FootItemID=@FootItemID, FootColor=@FootColor,
BackItemID=@BackItemID, BackColor=@BackColor,
HeadItemID=@HeadItemID, HeadColor=@HeadColor,
FaceItemID=@FaceItemID, FaceColor=@FaceColor,
EarringItemID=@EarringItemID, EarringColor=@EarringColor,
NeckItemID=@NeckItemID, NeckColor=@NeckColor,
FingerItemID=@FingerItemID, FingerColor=@FingerColor
WHERE CID=@CID; SET @RowCount = @@ROWCOUNT
IF( @RowCount < 1 OR @@ERROR <> 0 )
BEGIN
SELECT UpdateRowCount = -2;
GOTO ERROR_HANDLER;
END
COMMIT TRAN
SELECT UpdateRowCount = 1
return;
ERROR_HANDLER:
IF @@TRANCOUNT <> 0 ROLLBACK TRAN
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectChallengeQuestionNumber
(
@CID int
)
AS
BEGIN
SET NOCOUNT ON;
SELECT ChallengeQuestionID FROM ChallengeQuestionRecord WITH (NOLOCK)
WHERE CID = @CID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectCharacterBattleRecord
(
@CID int
)
AS
BEGIN
SET NOCOUNT ON;
SELECT Type, Value FROM CharBattleRecord WITH (NOLOCK)
WHERE CID = @CID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectCharacterList
(
@AID int
)
AS
BEGIN
select Charac.CID, Name, SlotIndex, Sex, Class, Charac.Level, CharPlayTime, AdminLevel, BP, Cash, LatestBattleType,
SetItemID, SetColor, UnderwearItemID, UnderwearColor, TopItemID, TopColor,
PantsItemID, PantsColor, HandItemID, HandColor, FootItemID, FootColor,
BackItemID, BackColor, HeadItemID, HeadColor, FaceItemID, FaceColor,
EarringItemID, EarringColor, NeckItemID, NeckColor, FingerItemID, FingerColor
from Character as Charac, CharacterLook as Look
where Charac.AID = @AID and Look.AID = @AID and Charac.CID = Look.CID and Charac.DeleteFlag = 0
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectChoiceProductList
(
@CID int
)
AS
BEGIN
SET NOCOUNT ON;
SELECT ChoiceProdID, Type, ProdSellPoliID, ProdID FROM ChoiceProduct WITH (NOLOCK)
WHERE CID = @CID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectFriendList
(
@CID int
)
AS
BEGIN
SELECT f.FriendCID, Name
FROM (SELECT FriendCID FROM Friend WHERE CID = @CID) AS f, Character
WHERE CID = f.FriendCID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectItemInstanceList
(
@CID int
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ExpirationTime int
SELECT IUID, ItemID, Durability, Color, SlotType, SlotID
, UsagePeriod, AddUsagePeriod, CharPlayTime, UseCharPlayTime, AccumuUsageTime
, ExpirationType, DATEDIFF(mi, GETDATE(), ExpiDate) AS ExpirationTime
FROM dbo.ItemInstance WITH (NOLOCK)
WHERE CID = @CID AND UsageState = 0 AND SlotType = 1;
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectKeeperList
AS
BEGIN
SELECT ID, IP, Port, Name FROM KeeperList
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectObserverList
(
@CID int
)
AS
BEGIN
SELECT CID FROM Friend WHERE FriendCID = @CID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectPatchSchedule
AS
BEGIN
SELECT PatchID, KeeperID, ProgramID, PatchTime, CycleMinute
FROM ServerPatchSchedule
WHERE CycleMinute <> 0 or datediff(minute, getdate(), PatchTime) > 0
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectProgramList
AS
BEGIN
SELECT ID, KeeperID, Name FROM ServerList
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spSelectServerPatchSchedule
(
@KeeperID int
)
AS
BEGIN
SELECT PatchID, ProgramID, PatchTime, CycleMinute
FROM ServerPatchSchedule
WHERE KeeperID = @KeeperID AND (CycleMinute <> 0 OR datediff(minute, getdate(), PatchTime) > 0)
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROC spTempInsertLogin
(
@ID nvarchar(20),
@PWD nvarchar(20)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FindAID int
SELECT @FindAID = AID FROM Login WHERE ID = @ID
IF (@FindAID is null)
BEGIN
INSERT INTO Login (ID, PWD) VALUES (@ID, @PWD)
SELECT aid FROM Login WHERE id = @ID
END
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROCEDURE spChangeWeaponSlot
(
@SlotID1 smallint, @IUID1 bigint, @SlotID2 smallint, @IUID2 bigint
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RowCount int
BEGIN TRAN
-- 1
UPDATE ItemInstance SET SlotID=@SlotID1 WHERE IUID=@IUID1; SET @RowCount = @@ROWCOUNT
IF( @RowCount < 1 OR @@ERROR <> 0 )
BEGIN
GOTO ERROR_HANDLER;
END
-- 2
IF( @IUID2 <> 0 )
BEGIN
UPDATE ItemInstance SET SlotID=@SlotID2 WHERE IUID=@IUID2; SET @RowCount = @@ROWCOUNT
IF( @RowCount < 1 OR @@ERROR <> 0 )
BEGIN
GOTO ERROR_HANDLER;
END
END
COMMIT TRAN
return;
ERROR_HANDLER:
IF @@TRANCOUNT <> 0 ROLLBACK TRAN
RAISERROR(N'Failed ChangeWeaponSlot Update IUID1(%d), IUID2(%d).'
, 11
, 1
, @IUID1, @IUID2);
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROCEDURE spSelectPlayerDataStageSetup
(
@CID int
)
AS
BEGIN
SELECT ID, CID, BattleType, Name, MapID, MaxPlayer, BattleRoundNum, BattleTime FROM PlayerDataStageSetup where CID = @CID
END
GO
-------------------------------------------------------------------------------------------------------
CREATE PROCEDURE spSavePlayerDataStageSetupUpdate
(
@ID int,
@MapID smallint,
@Name nvarchar(33),
@MaxPlayer smallint,
@BattleRoundNum smallint,
@BattleTime smallint
)
AS
BEGIN
UPDATE PlayerDataStageSetup SET MapID = @MapID, Name = @Name, MaxPlayer = @MaxPlayer, BattleRoundNum = @BattleRoundNum,
BattleTime = @BattleTime WHERE ID = @ID
END
CREATE PROCEDURE spSavePlayerDataStageSetupInsert
(
@CID int,
@BattleType tinyint,
@MapID smallint,
@Name nvarchar(33),
@MaxPlayer smallint,
@BattleRoundNum smallint,
@BattleTime smallint
)
AS
BEGIN
INSERT INTO PlayerDataStageSetup (CID, BattleType, MapID, Name, MaxPlayer, BattleRoundNum, BattleTime)
VALUES(@CID, @BattleType, @MapID, @Name, @MaxPlayer, @BattleRoundNum, @BattleTime )
END
-------------------------------------------------------------------------------------------------------
CREATE PROC spGameLogInsertBattleInfo
(
@MasterCID int,
@HostCID int,
@MasterServerGroupNum smallint,
@HostServerGroupNum smallint,
@MapID int,
@BattleType smallint,
@RoundCount smallint,
@PlayTime smallint
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @BattleUID bigint
INSERT INTO GameLog_BattleInfo (MasterCID, HostCID, MasterServerGroupNum, HostServerGroupNum, MapID, BattleType, RoundCount, PlayTime, StartDate, EndDate, IsCompleted)
VALUES (@MasterCID, @HostCID, @MasterServerGroupNum, @HostServerGroupNum, @MapID, @BattleType, @RoundCount, @PlayTime, getdate(), getdate(), 0)
SET @BattleUID = SCOPE_IDENTITY()
SELECT @BattleUID AS BattleUID
END
GO
-----------------------------------------------------------------------------------------------------------
CREATE PROC spGameLogUpdateEndBattle
(
@BattleUID bigint,
@IsCompleted tinyint
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RowCount int
UPDATE GameLog_BattleInfo SET IsCompleted=@IsCompleted, EndDate=GETDATE() WHERE BattleUID=@BattleUID;
SET @RowCount = @@ROWCOUNT
IF( @RowCount < 1 OR @@ERROR <> 0 )
BEGIN
RAISERROR(N'Failed spGameLogUpdateEndBattle Update BattleUID(%d).'
, 11
, 1
, @BattleUID);
END
END
GO
-----------------------------------------------------------------------------------------------------------
CREATE PROC spGameLogInsertKillDeath
(
@BattleUID BIGINT,
@RoundNumber_1 smallint, @AttackerCID_1 int, @VictimCID_1 int, @AttackerServerGroupNum_1 smallint, @VictimServerGroupNum_1 smallint,
@AttackerNPCID_1 int, @VictimNPCID_1 int, @WeaponID_1 int,
@RoundNumber_2 smallint, @AttackerCID_2 int, @VictimCID_2 int, @AttackerServerGroupNum_2 smallint, @VictimServerGroupNum_2 smallint,
@AttackerNPCID_2 int, @VictimNPCID_2 int, @WeaponID_2 int,
@RoundNumber_3 smallint, @AttackerCID_3 int, @VictimCID_3 int, @AttackerServerGroupNum_3 smallint, @VictimServerGroupNum_3 smallint,
@AttackerNPCID_3 int, @VictimNPCID_3 int, @WeaponID_3 int,
@RoundNumber_4 smallint, @AttackerCID_4 int, @VictimCID_4 int, @AttackerServerGroupNum_4 smallint, @VictimServerGroupNum_4 smallint,
@AttackerNPCID_4 int, @VictimNPCID_4 int, @WeaponID_4 int,
@RoundNumber_5 smallint, @AttackerCID_5 int, @VictimCID_5 int, @AttackerServerGroupNum_5 smallint, @VictimServerGroupNum_5 smallint,
@AttackerNPCID_5 int, @VictimNPCID_5 int, @WeaponID_5 int,
@RoundNumber_6 smallint, @AttackerCID_6 int, @VictimCID_6 int, @AttackerServerGroupNum_6 smallint, @VictimServerGroupNum_6 smallint,
@AttackerNPCID_6 int, @VictimNPCID_6 int, @WeaponID_6 int,
@RoundNumber_7 smallint, @AttackerCID_7 int, @VictimCID_7 int, @AttackerServerGroupNum_7 smallint, @VictimServerGroupNum_7 smallint,
@AttackerNPCID_7 int, @VictimNPCID_7 int, @WeaponID_7 int,
@RoundNumber_8 smallint, @AttackerCID_8 int, @VictimCID_8 int, @AttackerServerGroupNum_8 smallint, @VictimServerGroupNum_8 smallint,
@AttackerNPCID_8 int, @VictimNPCID_8 int, @WeaponID_8 int
)
AS
SET NOCOUNT ON;
WITH KLOG (RoundNumber, AttackerCID, VictimCID, AttackerServerGroupNum, VictimServerGroupNum, AttackerNPCID, VictimNPCID, WeaponID) AS
(
SELECT @RoundNumber_1, @AttackerCID_1, @VictimCID_1, @AttackerServerGroupNum_1, @VictimServerGroupNum_1, @AttackerNPCID_1, @VictimNPCID_1, @WeaponID_1
UNION ALL
SELECT @RoundNumber_2, @AttackerCID_2, @VictimCID_2, @AttackerServerGroupNum_2, @VictimServerGroupNum_2, @AttackerNPCID_2, @VictimNPCID_2, @WeaponID_2
UNION ALL
SELECT @RoundNumber_3, @AttackerCID_3, @VictimCID_3, @AttackerServerGroupNum_3, @VictimServerGroupNum_3, @AttackerNPCID_3, @VictimNPCID_3, @WeaponID_3
UNION ALL
SELECT @RoundNumber_4, @AttackerCID_4, @VictimCID_4, @AttackerServerGroupNum_4, @VictimServerGroupNum_4, @AttackerNPCID_4, @VictimNPCID_4, @WeaponID_4
UNION ALL
SELECT @RoundNumber_5, @AttackerCID_5, @VictimCID_5, @AttackerServerGroupNum_5, @VictimServerGroupNum_5, @AttackerNPCID_5, @VictimNPCID_5, @WeaponID_5
UNION ALL
SELECT @RoundNumber_6, @AttackerCID_6, @VictimCID_6, @AttackerServerGroupNum_6, @VictimServerGroupNum_6, @AttackerNPCID_6, @VictimNPCID_6, @WeaponID_6
UNION ALL
SELECT @RoundNumber_7, @AttackerCID_7, @VictimCID_7, @AttackerServerGroupNum_7, @VictimServerGroupNum_7, @AttackerNPCID_7, @VictimNPCID_7, @WeaponID_7
UNION ALL
SELECT @RoundNumber_8, @AttackerCID_8, @VictimCID_8, @AttackerServerGroupNum_8, @VictimServerGroupNum_8, @AttackerNPCID_8, @VictimNPCID_8, @WeaponID_8
)
INSERT INTO GameLog_KillDeath (BattleUID, RoundNumber, AttackerCID, VictimCID, AttackerServerGroupNum, VictimServerGroupNum, AttackerNPCID, VictimNPCID, WeaponID, REG_DATE)
SELECT @BattleUID, RoundNumber, AttackerCID, VictimCID, AttackerServerGroupNum, VictimServerGroupNum, AttackerNPCID, VictimNPCID, WeaponID, GETDATE()
FROM KLOG
WHERE WeaponID > 0;
GO
-----------------------------------------------------------------------------------------------------------
CREATE PROC spGameLogInsertWeaponShot
(
@BattleUID BIGINT,
@RoundNumber_1 smallint, @CID_1 int, @PlayerServerGroupNum_1 smallint, @WeaponID_1 int, @FireCount_1 int, @HitCount_1 int, @Damage_1 float,
@RoundNumber_2 smallint, @CID_2 int, @PlayerServerGroupNum_2 smallint, @WeaponID_2 int, @FireCount_2 int, @HitCount_2 int, @Damage_2 float,
@RoundNumber_3 smallint, @CID_3 int, @PlayerServerGroupNum_3 smallint, @WeaponID_3 int, @FireCount_3 int, @HitCount_3 int, @Damage_3 float,
@RoundNumber_4 smallint, @CID_4 int, @PlayerServerGroupNum_4 smallint, @WeaponID_4 int, @FireCount_4 int, @HitCount_4 int, @Damage_4 float,
@RoundNumber_5 smallint, @CID_5 int, @PlayerServerGroupNum_5 smallint, @WeaponID_5 int, @FireCount_5 int, @HitCount_5 int, @Damage_5 float,
@RoundNumber_6 smallint, @CID_6 int, @PlayerServerGroupNum_6 smallint, @WeaponID_6 int, @FireCount_6 int, @HitCount_6 int, @Damage_6 float,
@RoundNumber_7 smallint, @CID_7 int, @PlayerServerGroupNum_7 smallint, @WeaponID_7 int, @FireCount_7 int, @HitCount_7 int, @Damage_7 float,
@RoundNumber_8 smallint, @CID_8 int, @PlayerServerGroupNum_8 smallint, @WeaponID_8 int, @FireCount_8 int, @HitCount_8 int, @Damage_8 float,
@RoundNumber_9 smallint, @CID_9 int, @PlayerServerGroupNum_9 smallint, @WeaponID_9 int, @FireCount_9 int, @HitCount_9 int, @Damage_9 float,
@RoundNumber_10 smallint, @CID_10 int, @PlayerServerGroupNum_10 smallint, @WeaponID_10 int, @FireCount_10 int, @HitCount_10 int, @Damage_10 float,
@RoundNumber_11 smallint, @CID_11 int, @PlayerServerGroupNum_11 smallint, @WeaponID_11 int, @FireCount_11 int, @HitCount_11 int, @Damage_11 float,
@RoundNumber_12 smallint, @CID_12 int, @PlayerServerGroupNum_12 smallint, @WeaponID_12 int, @FireCount_12 int, @HitCount_12 int, @Damage_12 float
)
AS
SET NOCOUNT ON;
WITH KLOG (RoundNumber, CID, PlayerServerGroupNum, WeaponID, FireCount, HitCount, Damage) AS
(
SELECT @RoundNumber_1, @CID_1, @PlayerServerGroupNum_1, @WeaponID_1, @FireCount_1, @HitCount_1, @Damage_1
UNION ALL
SELECT @RoundNumber_2, @CID_2, @PlayerServerGroupNum_2, @WeaponID_2, @FireCount_2, @HitCount_2, @Damage_2
UNION ALL
SELECT @RoundNumber_3, @CID_3, @PlayerServerGroupNum_3, @WeaponID_3, @FireCount_3, @HitCount_3, @Damage_3
UNION ALL
SELECT @RoundNumber_4, @CID_4, @PlayerServerGroupNum_4, @WeaponID_4, @FireCount_4, @HitCount_4, @Damage_4
UNION ALL
SELECT @RoundNumber_5, @CID_5, @PlayerServerGroupNum_5, @WeaponID_5, @FireCount_5, @HitCount_5, @Damage_5
UNION ALL
SELECT @RoundNumber_6, @CID_6, @PlayerServerGroupNum_6, @WeaponID_6, @FireCount_6, @HitCount_6, @Damage_6
UNION ALL
SELECT @RoundNumber_7, @CID_7, @PlayerServerGroupNum_7, @WeaponID_7, @FireCount_7, @HitCount_7, @Damage_7
UNION ALL
SELECT @RoundNumber_8, @CID_8, @PlayerServerGroupNum_8, @WeaponID_8, @FireCount_8, @HitCount_8, @Damage_8
UNION ALL
SELECT @RoundNumber_9, @CID_9, @PlayerServerGroupNum_9, @WeaponID_9, @FireCount_9, @HitCount_9, @Damage_9
UNION ALL
SELECT @RoundNumber_10, @CID_10, @PlayerServerGroupNum_10, @WeaponID_10, @FireCount_10, @HitCount_10, @Damage_10
UNION ALL
SELECT @RoundNumber_11, @CID_11, @PlayerServerGroupNum_11, @WeaponID_11, @FireCount_11, @HitCount_11, @Damage_11
UNION ALL
SELECT @RoundNumber_12, @CID_12, @PlayerServerGroupNum_12, @WeaponID_12, @FireCount_12, @HitCount_12, @Damage_12
)
INSERT INTO GameLog_WeaponShot (BattleUID, RoundNumber, CID, PlayerServerGroupNum, WeaponID, FireCount, HitCount, Damage, REG_DATE)
SELECT @BattleUID, RoundNumber, CID, PlayerServerGroupNum, WeaponID, FireCount, HitCount, Damage, GETDATE()
FROM KLOG
WHERE CID > 0;
GO
-----------------------------------------------------------------------------------------------------------
CREATE PROC spGameLogInsertWeaponTime
(
@BattleUID BIGINT,
@CID_1 int, @PlayerServerGroupNum_1 smallint, @WeaponID_1 int, @UseTime_1 int,
@CID_2 int, @PlayerServerGroupNum_2 smallint, @WeaponID_2 int, @UseTime_2 int,
@CID_3 int, @PlayerServerGroupNum_3 smallint, @WeaponID_3 int, @UseTime_3 int,
@CID_4 int, @PlayerServerGroupNum_4 smallint, @WeaponID_4 int, @UseTime_4 int,
@CID_5 int, @PlayerServerGroupNum_5 smallint, @WeaponID_5 int, @UseTime_5 int,
@CID_6 int, @PlayerServerGroupNum_6 smallint, @WeaponID_6 int, @UseTime_6 int,
@CID_7 int, @PlayerServerGroupNum_7 smallint, @WeaponID_7 int, @UseTime_7 int,
@CID_8 int, @PlayerServerGroupNum_8 smallint, @WeaponID_8 int, @UseTime_8 int,
@CID_9 int, @PlayerServerGroupNum_9 smallint, @WeaponID_9 int, @UseTime_9 int,
@CID_10 int, @PlayerServerGroupNum_10 smallint, @WeaponID_10 int, @UseTime_10 int,
@CID_11 int, @PlayerServerGroupNum_11 smallint, @WeaponID_11 int, @UseTime_11 int,
@CID_12 int, @PlayerServerGroupNum_12 smallint, @WeaponID_12 int, @UseTime_12 int
)
AS
SET NOCOUNT ON;
WITH KLOG (CID, PlayerServerGroupNum, WeaponID, UseTime) AS
(
SELECT @CID_1, @PlayerServerGroupNum_1, @WeaponID_1, @UseTime_1
UNION ALL
SELECT @CID_2, @PlayerServerGroupNum_2, @WeaponID_2, @UseTime_2
UNION ALL
SELECT @CID_3, @PlayerServerGroupNum_3, @WeaponID_3, @UseTime_3
UNION ALL
SELECT @CID_4, @PlayerServerGroupNum_4, @WeaponID_4, @UseTime_4
UNION ALL
SELECT @CID_5, @PlayerServerGroupNum_5, @WeaponID_5, @UseTime_5
UNION ALL
SELECT @CID_6, @PlayerServerGroupNum_6, @WeaponID_6, @UseTime_6
UNION ALL
SELECT @CID_7, @PlayerServerGroupNum_7, @WeaponID_7, @UseTime_7
UNION ALL
SELECT @CID_8, @PlayerServerGroupNum_8, @WeaponID_8, @UseTime_8
UNION ALL
SELECT @CID_9, @PlayerServerGroupNum_9, @WeaponID_9, @UseTime_9
UNION ALL
SELECT @CID_10, @PlayerServerGroupNum_10, @WeaponID_10, @UseTime_10
UNION ALL
SELECT @CID_11, @PlayerServerGroupNum_11, @WeaponID_11, @UseTime_11
UNION ALL
SELECT @CID_12, @PlayerServerGroupNum_12, @WeaponID_12, @UseTime_12
)
INSERT INTO GameLog_WeaponTime (BattleUID, CID, PlayerServerGroupNum, WeaponID, UseTime, REG_DATE)
SELECT @BattleUID, CID, PlayerServerGroupNum, WeaponID, UseTime, GETDATE()
FROM KLOG
WHERE CID > 0;
GO
-----------------------------------------------------------------------------------------------------------
