Hi everyone, I'm trying to fix this SQL code to generate new player buffs for season 17 of IGCN. However it is not working, hope someone can help me. Thank you
USE [MuOnline]
GO
/****** Object: StoredProcedure [dbo].[WZ_CreateCharacter] Script Date: 10/10/2022 1:45:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[WZ_CreateCharacter]
@AccountID varchar(10),
@Name varchar(10),
@Class tinyint
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @Result tinyint
SET @Result = 0x00
IF EXISTS (SELECT Name FROM Character WHERE Name = @Name)
BEGIN
SET @Result = 0x01
GOTO ProcEnd
END
BEGIN TRAN
IF NOT EXISTS (SELECT Id FROM AccountCharacter WHERE Id = @AccountID)
BEGIN
INSERT INTO dbo.AccountCharacter(Id, GameID1, GameID2, GameID3, GameID4, GameID5, GameID6, GameID7, GameID8,GameID9, GameID10, GameIDC)
VALUES(@AccountID, @Name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
SET @Result = @@Error
END
ELSE
BEGIN
DECLARE @g1 varchar(10), @g2 varchar(10), @g3 varchar(10), @g4 varchar(10), @g5 varchar(10), @g6 varchar(10), @g7 varchar(10), @g8 varchar(10), @g9 varchar(10), @g10 varchar(10)
SELECT @g1=GameID1, @g2=GameID2, @g3=GameID3, @g4=GameID4, @g5=GameID5, @g6=GameID6, @g7=GameID7, @g8=GameID8, @g9=GameID9, @g10=GameID10 FROM dbo.AccountCharacter WHERE Id = @AccountID
IF((@g1 is NULL) OR (Len(@g1) = 0))
BEGIN
UPDATE AccountCharacter SET GameID1 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g2 is NULL OR Len(@g2) = 0)
BEGIN
UPDATE AccountCharacter SET GameID2 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g3 is NULL OR Len(@g3) = 0)
BEGIN
UPDATE AccountCharacter SET GameID3 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g4 is NULL OR Len(@g4) = 0)
BEGIN
UPDATE AccountCharacter SET GameID4 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g5 is NULL OR Len(@g5) = 0)
BEGIN
UPDATE AccountCharacter SET GameID5 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g6 is NULL OR Len(@g6) = 0)
BEGIN
UPDATE AccountCharacter SET GameID6 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g7 is NULL OR Len(@g7) = 0)
BEGIN
UPDATE AccountCharacter SET GameID7 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g8 is NULL OR Len(@g8) = 0)
BEGIN
UPDATE AccountCharacter SET GameID8 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g9 is NULL OR Len(@g9) = 0)
BEGIN
UPDATE AccountCharacter SET GameID9 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE IF(@g10 is NULL OR Len(@g10) = 0)
BEGIN
UPDATE AccountCharacter SET GameID10 = @Name
WHERE Id = @AccountID
SET @Result = @@Error
END
ELSE
BEGIN
SET @Result = 0x03
GOTO TranProcEnd
END
END
IF(@Result <> 0)
BEGIN
GOTO TranProcEnd
END
ELSE
BEGIN
INSERT INTO dbo.Character(AccountID, Name, cLevel, LevelUpPoint, Class, Strength, Dexterity, Vitality, Energy, Inventory, MagicList,
Life, MaxLife, Mana, MaxMana, MapNumber, MapPosX, MapPosY, MDate, LDate, Quest, Leadership , Money)
SELECT @AccountID AS AccountID, @Name AS Name, Level, LevelUpPoint, @Class AS Class, Strength, Dexterity, Vitality, Energy, Inventory,
MagicList, Life, MaxLife, Mana, MaxMana, 0, 182, 128, getdate() AS MDate, getdate() AS LDate, Quest, Leadership, 3000000
FROM DefaultClassType WHERE Class = @Class
SET @Result = @@Error
END
/* CUSTOM ( Creater character ) */
DECLARE @Items TABLE
(
[index] int IDENTITY (1, 1),
[itemCode] int,
[EffectType1] tinyint,
[EffectType2] tinyint,
[OptionType] tinyint,
[itemType] tinyint,
[ExpireDays] int
)
DECLARE @ItemsInventory TABLE
(
[RowIndex] int IDENTITY (1, 1),
[Class] int,
[itemCode] int,
[ExpireDays] int
)
/** Selos **/
INSERT INTO @Items ([itemCode], [EffectType1], [EffectType2], [OptionType], [itemType], [ExpireDays])
VALUES
/* (Item Code, Effect Type 1, Effect Type 2, Option Type, Item Type, Date in Days for expirate), */
/* SEAL REWAD CHARACTER */
(6718, 6, 53, 0, 1, 1), /* SEAL OF HEALING 1 ( Day ) */
/* SCROLL REWAD CHARACTER */
(7265, 55, 0, 0, 1, 1), /* SCROLL OF BATTLE 1 ( Day ) */
(7242, 14, 0, 0, 1, 1), /* SCROLL OF WRATH 1 ( Day ) */
(7241, 3, 0, 0, 1, 1), /* SEAL OF DEFENSE 1 ( Day ) */
(7240, 1, 0, 0, 1, 1), /* SEAL OF QUICKNESS 1 ( Day ) */
(7243, 15, 0, 0, 1, 1), /* SEAL OF WIZARDRY 1 ( Day ) */
(7244, 4, 0, 0, 1, 1), /* SEAL OF HEALTH 1 ( Day ) */
(7245, 5, 0, 0, 1, 1), /* SEAL OF MANA 1 ( Day ) */
(7266, 56, 0, 0, 1, 1) /* SEAL OF HEALING 1 ( Day ) */
/*******************************************************/
DECLARE @UserGuid int = (SELECT [memb_guid] FROM [MuOnline].[dbo].[MEMB_INFO] WHERE [memb___id] = @AccountID)
DECLARE @ItemCount int = (SELECT COUNT(1) FROM @Items)
DECLARE @LoopIndex int = 1
DECLARE @GetItemSerialResult TABLE
(
[itemSerial] bigint
)
DECLARE @ItemCode int
DECLARE @EffectType1 tinyint
DECLARE @EffectType2 tinyint
DECLARE @OptionType tinyint
DECLARE @ItemType tinyint
DECLARE @ExpireDate smalldatetime
WHILE (@LoopIndex <= @ItemCount)
BEGIN
SELECT
@ItemCode = [itemCode],
@EffectType1 = [EffectType1],
@EffectType2 = [EffectType2],
@OptionType = [OptionType],
@ItemType = [itemType],
@ExpireDate = DATEADD(DAY, [ExpireDays], GETUTCDATE())
FROM @Items
WHERE [index] = @LoopIndex
DELETE FROM @GetItemSerialResult
INSERT INTO @GetItemSerialResult
EXECUTE [dbo].[WZ_GetItemSerial]
INSERT INTO dbo.IGC_PeriodItemInfo (
[userGuid],
[CharacterName],
[itemCode],
[EffectType1],
[EffectType2],
[usedTime],
[LeftTime],
[buyDate],
[ExpireDate],
[usedInfo],
[OptionType],
[itemType],
[serialCode],
[buyDateConvert],
[ExpireDateConvert],
[setExpire]
) VALUES (
@UserGuid,
@Name,
@ItemCode,
@EffectType1,
@EffectType2,
0,
DATEDIFF(SECOND, GETUTCDATE(), @ExpireDate),
GETUTCDATE(),
@ExpireDate,
1,
@OptionType,
@ItemType,
(SELECT [itemSerial] FROM @GetItemSerialResult),
DATEDIFF(SECOND, '1970-01-01 00:00:00', GETUTCDATE()),
DATEDIFF(SECOND, '1970-01-01 00:00:00', @ExpireDate),
0
)
SET @LoopIndex = @LoopIndex + 1
END
IF ( @@Error <> 0 ) BEGIN
SET @Result = @@Error
END
/* CHECK AND INSERT LOGINS TO T_InGameShop_Point */
IF NOT EXISTS ( SELECT AccountID FROM T_InGameShop_Point WHERE AccountID =@AccountID )
BEGIN
INSERT INTO dbo.T_InGameShop_Point (AccountID, WCoin, GoblinPoint)
VALUES (@AccountID, 0, 0 )
END
/* CHECK AND INSERT LOGINS TO IGC_EvolutionMonster */
IF NOT EXISTS ( SELECT AccountID FROM IGC_EvolutionMonster WHERE AccountID = @AccountID and Name = @Name )
BEGIN
INSERT INTO dbo.IGC_EvolutionMonster ( AccountID, Name)
VALUES (@AccountID, @Name)
END
TranProcEnd:
IF (@Result <> 0)
ROLLBACK TRAN
ELSE
COMMIT TRAN
ProcEnd:
SET NOCOUNT OFF
SET XACT_ABORT OFF
SELECT
CASE @Result
WHEN 0x00 THEN 0x01
WHEN 0x01 THEN 0x00
WHEN 0x03 THEN 0x03
ELSE 0x02
END AS Result
END
Tema: SQL Script starter buff (Leído 486 veces)
