stored procedures causing item bug
especially STRG_MOVE_ITEM
this is my STRG_MOVE_ITEM's sql query
Quote:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------------------------------------------------------------
-- PROC #3
-- _STRG_MOVE_ITEM ÇÁ·Î½ÃÁ®¸¦ ¼öÁ¤ÇÏ¿© ¾Æ¹ÙŸ Àκ¥Å丮·Î ¾ÆÀÌÅÛ À̵¿ ±â´ÉÀ» Ãß°¡ÇØ ÁÖÀÚ! - by zone0000 (Çã½Â¿í)
--------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------
-- _STRG_MOVE_ITEM ¾Æ¹ÙŸ Operation(35)(36) Ãß°¡ (Çã½Â¿í)
-----------------------------------------------------------------
ALTER PROCEDURE [dbo].[_STRG_MOVE_ITEM]
@Operation AS TINYINT,
@UserJID AS INT,
@CharID AS INT,
@GuildID AS BIGINT, -- Operation 39, 40¿¡¼± LinkedItemID·Î »ç¿ë.
@Slot_From AS TINYINT,
@Slot_To AS TINYINT,
@Data AS INT, -- ºÎ¼*Áø Àåºñµµ °¡´ÉÇϱⶫ¿¡ 0 ÀÌ µÉ ¼ö ÀÖ´Ù.
@RemoveAndOverwrite AS TINYINT
AS
----------------
-- SP_UPDATE_SLOTS_INV 0
-- SP_UPDATE_SLOTS_CHEST 1
-- SP_DEPOSIT_ITEM 2
-- SP_WITHDRAW_ITEM 3
-- SP_UPDATE_SLOTS_INV_COS 16
-- SP_GUILD_CHEST_UPDATE_SLOT 29
-- SP_GUILD_CHEST_DEPOSIT_ITEM 30
-- SP_GUILD_CHEST_WITHDRAW_ITEM 31
-- SP_MOVE_ITEM_AVATAR_PC 35 -- Avatar Ãß°¡
-- SP_MOVE_ITEM_PC_AVATAR 36 -- Avatar Ãß°¡
-- SP_PUSH_ITEM_INTO_MAGIC_CUBE 39 -- MagicCubeÃß°¡
-- SP_POP_ITEM_FROM_MAGIC_CUBE 40 -- MagicCubeÃß°¡
----------------
IF (@Operation <> 0 AND @Operation <> 1 AND @Operation <> 2 AND @Operation <> 3 AND @Operation <> 16 AND @Operation <> 29 AND @Operation <> 30 AND @Operation <> 31
AND @Operation <> 35 AND @Operation <> 36 -- Avatar Ãß°¡
AND @Operation <> 39 AND @Operation <> 40 ) -- MagicCubeÃß°¡
BEGIN
RETURN -1000
END
-- overwrite ´Â ¿ÀÁ÷ inventory ¾È¿¡¼*¸¸ ÀϾ ¼ö ÀÖ´Â ÀÏÀÌ´Ù(È*»ì ÀÚµ¿ reloading)
IF (@RemoveAndOverwrite <> 0 AND @Operation <> 0)
BEGIN
RETURN -1001
END
DECLARE @ItemID_From BIGINT
DECLARE @ItemID_To BIGINT
SET @ItemID_From = 0
SET @ItemID_To = 0
----------------------------------------------------------
-- Step1. Src Slot °ú Dst Slot ÀÇ ¾ÆÀÌÅÛ ¾ò±â ¹× Á¸Àç ¹«°á¼º °Ë»ç
----------------------------------------------------------
-------------- Src Item ¾ò±â
IF (@Operation = 0 OR @Operation = 2 OR @Operation = 30) -- src: inventory
BEGIN
SELECT @ItemID_From = ItemID FROM _Inventory WHERE CharID = @CharID AND Slot = @Slot_From
END
ELSE IF (@Operation = 1 OR @Operation = 3) -- src: chest
BEGIN
SELECT @ItemID_From = ItemID FROM _Chest WHERE UserJID = @UserJID AND Slot = @Slot_From
END
ELSE IF (@Operation = 16)
BEGIN
SELECT @ItemID_From = ItemID FROM _InvCOS WHERE COSID = @CharID AND Slot = @Slot_From
END
ELSE IF (@Operation = 29 OR @Operation = 31)
BEGIN
SELECT @ItemID_From = ItemID FROM _GuildChest WHERE GuildID = @GuildID AND Slot = @Slot_From
END
-- Avatar Ãß°¡ ( Avatar -> PC )
ELSE IF (@Operation = 35)
BEGIN
SELECT @ItemID_From = ItemID FROM _InventoryForAvatar WHERE CharID = @CharID AND Slot = @Slot_From
END
-- Avatar Ãß°¡ ( PC -> Avatar )
ELSE IF (@Operation = 36)
BEGIN
SELECT @ItemID_From = ItemID FROM _Inventory WHERE CharID = @CharID AND Slot = @Slot_From
END
-- MagicCube Ãß°¡( PCInv -> MagicCube )
ELSE IF (@Operation = 39)
BEGIN
SELECT @ItemID_From = ItemID FROM _Inventory WHERE CharID = @CharID AND Slot = @Slot_From
END
-- MagicCube Ãß°¡( MagicCube -> PCInv )
ELSE IF (@Operation = 40)
BEGIN
SELECT @ItemID_From = ItemID FROM _InventoryForLinkedStorage WHERE LinkedItemID = @GuildID AND Slot = @Slot_From
END
-------------- Dst Item ¾ò±â
IF (@Operation = 0 OR @Operation = 3 OR @Operation = 31) -- dst: inventory
BEGIN
SELECT @ItemID_To = ItemID FROM _Inventory WHERE CharID = @CharID AND Slot = @Slot_To
END
ELSE IF (@Operation = 1 OR @Operation = 2) -- dst: chest
BEGIN
SELECT @ItemID_To = ItemID FROM _Chest WHERE UserJID = @UserJID AND Slot = @Slot_To
END
ELSE IF (@Operation = 16)
BEGIN
SELECT @ItemID_To = ItemID FROM _InvCOS WHERE COSID = @CharID AND Slot = @Slot_To
END
ELSE IF (@Operation = 29 OR @Operation = 30)
BEGIN
SELECT @ItemID_To = ItemID FROM _GuildChest WHERE GuildID = @GuildID AND Slot = @Slot_To
END
-- Avatar Ãß°¡ ( Avatar -> PC )
ELSE IF (@Operation = 35)
BEGIN
SELECT @ItemID_To = ItemID FROM _Inventory WHERE CharID = @CharID AND Slot = @Slot_To
END
-- Avatar Ãß°¡ ( PC -> Avatar )
ELSE IF (@Operation = 36)
BEGIN
SELECT @ItemID_To = ItemID FROM _InventoryForAvatar WHERE CharID = @CharID AND Slot = @Slot_To
END
-- MagicCube Ãß°¡( PCInv -> MagicCube )
ELSE IF (@Operation = 39)
BEGIN
SELECT @ItemID_To = ItemID FROM _InventoryForLinkedStorage WHERE LinkedItemID = @GuildID AND Slot = @Slot_To
END
-- MagicCube Ãß°¡( MagicCube -> PCInv )
ELSE IF (@Operation = 40)
BEGIN
SELECT @ItemID_To = ItemID FROM _Inventory WHERE CharID = @CharID AND Slot = @Slot_To
END
-------------- ¹«°á¼º °Ë»ç
IF (@RemoveAndOverwrite <> 0)
BEGIN
-- µÎ ½½·Ô ¸ðµÎ ¾ÆÀÌÅÛÀÌ Á¸ÀçÇØ¾ß ÇÑ´Ù.
IF (@ItemID_From IS NULL OR @ItemID_To IS NULL)
BEGIN
RETURN -1003
END
IF (@ItemID_From = 0 OR @ItemID_To = 0)
BEGIN
RETURN -1004
END
END
ELSE
BEGIN
-- Source ½½·Ô¿¡¸¸ ¾ÆÀÌÅÛÀÌ Á¸ÀçÇØ¾ß ÇÑ´Ù.
IF (@ItemID_From IS NULL OR @ItemID_From = 0)
BEGIN
RETURN -1005
END
IF (@ItemID_To IS NOT NULL AND @ItemID_To <> 0)
BEGIN
RETURN -1006
END
END
IF (@Operation = 0)
BEGIN
IF (@Slot_To < 13)
BEGIN
DECLARE @CheckValidity INT
EXEC @CheckValidity = _STRG_CHECKITEM_CANBE_EQUIPED @ItemID_From, @Slot_To
IF (@CheckValidity <= 0)
BEGIN
RETURN @CheckValidity
END
END
END
BEGIN TRANSACTION
----------------------------------------------------------
-- Step2. Storage ¾÷µ¥ÀÌÆ®
----------------------------------------------------------
-------------- Src Slot °»½Å
IF (@Operation = 0 OR @Operation = 2 OR @Operation = 30)
BEGIN
UPDATE _Inventory SET ItemID = 0 WHERE CharID = @CharID AND Slot = @Slot_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
ELSE IF (@Operation = 1 OR @Operation = 3)
BEGIN
UPDATE _Chest SET ItemID = 0 WHERE UserJID = @UserJID AND Slot = @Slot_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
ELSE IF (@Operation = 16)
BEGIN
UPDATE _InvCOS SET ItemID = 0 WHERE COSID = @CharID AND Slot = @Slot_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
ELSE IF (@Operation = 29 OR @Operation = 31)
BEGIN
UPDATE _GuildChest SET ItemID = 0 WHERE GuildID = @GuildID AND Slot = @Slot_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
-- Avatar Ãß°¡ ( Avatar -> PC )
ELSE IF (@Operation = 35 )
BEGIN
UPDATE _InventoryForAvatar SET ItemID = 0 WHERE CharID = @CharID AND Slot = @Slot_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
-- Avatar Ãß°¡ ( PC -> Avatar )
ELSE IF (@Operation = 36 )
BEGIN
UPDATE _Inventory SET ItemID = 0 WHERE CharID = @CharID AND Slot = @Slot_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
-- MagicCube Ãß°¡( PCInv -> MagicCube )
ELSE IF (@Operation = 39 )
BEGIN
UPDATE _Inventory SET ItemID = 0 WHERE CharID = @CharID AND Slot = @Slot_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
-- MagicCube Ãß°¡( MagicCube -> PCInv )
ELSE IF (@Operation = 40 )
BEGIN
UPDATE _InventoryForLinkedStorage SET ItemID = 0 WHERE LinkedItemID = @GuildID AND Slot = @Slot_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
END
-------------- Dst Slot °»½Å
IF (@Operation = 0 OR @Operation = 3 OR @Operation = 31)
BEGIN
UPDATE _Inventory SET ItemID = @ItemID_From WHERE CharID = @CharID AND Slot = @Slot_To
END
ELSE IF (@Operation = 1 OR @Operation = 2)
BEGIN
UPDATE _Chest SET ItemID = @ItemID_From WHERE UserJID = @UserJID AND Slot = @Slot_To
END
ELSE IF (@Operation = 16)
BEGIN
UPDATE _InvCOS SET ItemID = @ItemID_From WHERE COSID = @CharID AND Slot = @Slot_To
END
ELSE IF (@Operation = 29 OR @Operation = 30)
BEGIN
UPDATE _GuildChest SET ItemID = @ItemID_From WHERE GuildID = @GuildID AND Slot = @Slot_To
END
-- Avatar Ãß°¡ ( Avatar -> PC )
ELSE IF (@Operation = 35)
BEGIN
UPDATE _Inventory SET ItemID = @ItemID_From WHERE CharID = @CharID AND Slot = @Slot_To
END
-- Avatar Ãß°¡ ( PC -> Avatar )
ELSE IF (@Operation = 36)
BEGIN
UPDATE _InventoryForAvatar SET ItemID = @ItemID_From WHERE CharID = @CharID AND Slot = @Slot_To
END
-- MagicCube Ãß°¡( PCInv -> MagicCube )
ELSE IF (@Operation = 39)
BEGIN
UPDATE _InventoryForLinkedStorage SET ItemID = @ItemID_From WHERE LinkedItemID = @GuildID AND Slot = @Slot_To
END
-- MagicCube Ãß°¡( MagicCube -> PCInv )
ELSE IF (@Operation = 40)
BEGIN
UPDATE _Inventory SET ItemID = @ItemID_From WHERE CharID = @CharID AND Slot = @Slot_To
END
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -3
END
----------------------------------------------------------
-- Step3. Overwrite ÇØ¾ß ÇÏ´Â °æ¿ì ¿ø·¡ ´ë»ó ½½·Ô¿¡ ÀÖ´ø ¾ÆÀÌÅÛ »èÁ¦!
----------------------------------------------------------
IF (@RemoveAndOverwrite <> 0)
BEGIN
DECLARE @Rvalue INT
EXEC @Rvalue = _STRG_FREE_ITEM_NoTX @ItemID_To
IF (@Rvalue < 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -4
END
END
----------------------------------------------------------
-- Step4. 4Byte Item Data °»½Å (±âÁ¸ °ªÀÌ º¯°æµÈ °æ¿ì¸¸!)
----------------------------------------------------------
DECLARE @Old_ItemData INT
SELECT @Old_ItemData = Data FROM _ITEMS WHERE ID64 = @ItemID_From
IF (@Old_ItemData IS NOT NULL AND @Old_ItemData <> @Data)
BEGIN
UPDATE _Items SET Data = @Data WHERE ID64 = @ItemID_From
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -4
END
END
----------------------------------------------------------
-- Step4. COMMIT
----------------------------------------------------------
COMMIT TRANSACTION
RETURN 1
|
whats wrong with it ?
![[Question] About the item bug.](https://www.elitepvpers.com/forum/iconimages/sro-private-server/question-about-item-bug_ltr.gif){kind=small}
