[SQL] Partially working USP_ITEM_RECYCLE

[chreadie]

When i wrote partially i ment THIS SP IS NOT WORKING SO EITHER FIX IT OR STOP ASKING ME!

Here is my partially working USP_ITEM_RECYCLE, it comes without guarantees.

1. It works partially because no transactions are made or rolled back
2. I think im close to finding the return of the SP

Because i dont have alot of time right now and i had a request for the source of the SP, here it is:

Spoiler:

USE [Pangya_S4_TH]
GO
/****** Object: StoredProcedure [dbo].[USP_ITEM_RECYCLE] Script Date: 03/05/2011 21:11:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROC [dbo].[USP_ITEM_RECYCLE] (
@UID AS INT ,
@RECYCLEDATA VARCHAR(8000)
) -- WITH ENCRYPTION
AS
/*********************
ADD, 20080219 AMY HAEJIN HYUN
CARD V1.1 카드 붙은 의상: 마법상자

2009-05-26, GOMI97
- @@IDENTITY 패치

295 SET @ITEM_ID_0 = @@IDENTITY
307 SET @ITEM_ID_0 = @@IDENTITY
315 SET @ITEM_ID_0 = @@IDENTITY

**********************/
BEGIN
SET NOCOUNT ON

-- 0. 카디에의 마법상자
DECLARE @ITEMS INT
DECLARE @ITEM_ID_1 bigint
DECLARE @TYPEID_1 int
DECLARE @C0_1 smallint
DECLARE @ISPARTS_1 smallint
DECLARE @ITEM_ID_2 bigint
DECLARE @TYPEID_2 int
DECLARE @C0_2 smallint
DECLARE @ISPARTS_2 smallint
DECLARE @ITEM_ID_3 bigint
DECLARE @TYPEID_3 int
DECLARE @C0_3 smallint
DECLARE @ISPARTS_3 smallint
DECLARE @ITEM_ID_4 bigint
DECLARE @TYPEID_4 int
DECLARE @C0_4 smallint
DECLARE @ISPARTS_4 smallint
DECLARE @ITEM_ID_0 bigint
DECLARE @TYPEID_0 int
DECLARE @C0_0 smallint
DECLARE @ISPARTS_0 smallint

SET @C0_1 = 0
SET @ISPARTS_1 = 0
SET @ITEM_ID_1 = 0
SET @TYPEID_1 = 0
SET @C0_2 = 0
SET @ISPARTS_2 = 0
SET @ITEM_ID_2 = 0
SET @TYPEID_2 = 0
SET @C0_2 = 0
SET @ISPARTS_3 = 0
SET @ITEM_ID_3 = 0
SET @TYPEID_3 = 0
SET @C0_4 = 0
SET @ISPARTS_4 = 0
SET @ITEM_ID_4 = 0
SET @TYPEID_4 = 0


DECLARE @hdlXmlDoc INT
--DECLARE @Entry2 VARCHAR(1000)

exec sp_xml_preparedocument @hdlXmlDoc OUTPUT, @RECYCLEDATA
SELECT @C0_1 = C0, @ISPARTS_1 = IsParts, @ITEM_ID_1 = Item_ID, @TYPEID_1 = TypeID FROM Openxml(@hdlXmlDoc,'/RECYCLE/MINUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 1
SELECT @C0_2 = C0, @ISPARTS_2 = IsParts, @ITEM_ID_2 = Item_ID, @TYPEID_2 = TypeID FROM Openxml(@hdlXmlDoc,'/RECYCLE/MINUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 2
SELECT @C0_3 = ISNULL(C0,0), @ISPARTS_3 = ISNULL(IsParts,0), @ITEM_ID_3 = ISNULL(Item_ID,0), @TYPEID_3 = ISNULL(TypeID,0) FROM Openxml(@hdlXmlDoc,'/RECYCLE/MINUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 3
SELECT @C0_4 = C0, @ISPARTS_4 = ISNULL(IsParts,0), @ITEM_ID_4 = ISNULL(Item_ID,0), @TYPEID_4 = ISNULL(TypeID,0) FROM Openxml(@hdlXmlDoc,'/RECYCLE/MINUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 4
SELECT @C0_0 = C0, @ISPARTS_0 = IsParts, @ITEM_ID_0 = Item_ID, @TYPEID_0 = TypeID FROM Openxml(@hdlXmlDoc,'/RECYCLE/PLUS')
WITH (SEQ int '@SEQ',
TypeID BIGINT '@TYPEID',
Item_ID INT '@ITEM_ID',
C0 int '@C0',
IsParts int '@ISPARTS')
WHERE SEQ = 1
EXEC sp_xml_removedocument @hdlXmlDoc




DECLARE @C0_1_ORI SMALLINT
, @C0_2_ORI SMALLINT
, @C0_3_ORI SMALLINT
, @C0_4_ORI SMALLINT

-- 0 : SUCCESS , 1 : FAILURE
DECLARE @RET_1 TINYINT
, @RET_2 TINYINT
, @RET_3 TINYINT
, @RET_4 TINYINT

DECLARE @CADDIECLEVEL TINYINT
, @CADDIEEXP INT

DECLARE @RET_0 AS TINYINT





-- INITIALIZE - 초기화
SET @RET_1 = 1
SET @RET_2 = 1
SET @RET_3 = 1
SET @RET_4 = 1
SET @RET_0 = 1


---- 1. CHECK THE ITEM CAN BE CONSUMED. ------------------------------------------------------------------------------
IF (@ITEM_ID_1 > 0) BEGIN
IF ( @ISPARTS_1 = 3 ) BEGIN
SELECT TOP 1 @C0_1_ORI = 1
, @CADDIECLEVEL = CLEVEL
, @CADDIEEXP = [EXP]
FROM DBO.PANGYA_CADDIE_INFO WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_1
AND TYPEID = @TYPEID_1
AND VALID = 1

-- 아이템이 삭제할 개수만큼 있어야
IF ( @@ROWCOUNT = 1 ) BEGIN
SET @RET_1 = 0 -- 성공
END ELSE BEGIN
SET @RET_1 = 2 -- 실패
END

END ELSE BEGIN
SELECT TOP 1 @C0_1_ORI = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_1
AND TYPEID = @TYPEID_1 AND VALID = 1

IF ( @@ROWCOUNT = 1 ) -- 아이템이 삭제할 개수만큼 있어야
AND ( ( @C0_1_ORI >= @C0_1 AND @ISPARTS_1 = 0 ) OR ( @ISPARTS_1 = 1) ) BEGIN
SET @RET_1 = 0 -- 성공
END ELSE BEGIN
SET @RET_1 = 2 -- 실패
END
END
END

---- 2. CHECK THE ITEM CAN BE CONSUMED. ------------------------------------------------------------------------------
IF (@ITEM_ID_2 > 0) BEGIN
SELECT TOP 1 @C0_2_ORI = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_2
AND TYPEID = @TYPEID_2
AND VALID = 1

IF ( @@ROWCOUNT = 1 ) -- 아이템이 삭제할 개수만큼 있어야
AND ( ( @C0_2_ORI >= @C0_2 AND @ISPARTS_2 = 0 ) OR ( @ISPARTS_2 = 1) ) BEGIN
SET @RET_2 = 0 -- 성공
END ELSE BEGIN
SET @RET_2 = 2 -- 실패
END
END

---- 3. CHECK THE ITEM CAN BE CONSUMED. ------------------------------------------------------------------------------
IF (@ITEM_ID_3 > 0) BEGIN
SELECT TOP 1 @C0_3_ORI = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_3
AND TYPEID = @TYPEID_3
AND VALID = 1

IF ( @@ROWCOUNT = 1 ) -- 아이템이 삭제할 개수만큼 있어야
AND ( ( @C0_3_ORI >= @C0_3 AND @ISPARTS_3 = 0 ) OR ( @ISPARTS_3 = 1) ) BEGIN
SET @RET_3 = 0 -- 성공
END ELSE BEGIN
SET @RET_3 = 2 -- 실패
END
END

---- 4. CHECK THE ITEM CAN BE CONSUMED. ------------------------------------------------------------------------------
IF (@ITEM_ID_4 > 0) BEGIN
SELECT TOP 1 @C0_4_ORI = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE WITH(READUNCOMMITTED)
WHERE UID = @UID
AND ITEM_ID = @ITEM_ID_4
AND TYPEID = @TYPEID_4
AND VALID = 1

IF ( @@ROWCOUNT = 1 ) -- 아이템이 삭제할 개수만큼 있어야
AND ( ( @C0_4_ORI >= @C0_4 AND @ISPARTS_4 = 0 ) OR ( @ISPARTS_4 = 1) ) BEGIN
SET @RET_4 = 0 -- 성공
END ELSE BEGIN
SET @RET_4 = 2 -- 실패
END
END



IF ( ( @RET_1 IN (0, 1) )
AND ( @RET_2 IN (0, 1) )
AND ( @RET_3 IN (0, 1) )
AND ( @RET_4 IN (0, 1) ) ) BEGIN
-- 0.
SET @RET_0 = 0

---- 1. CONSUME THE ITEM. ------------------------------------------------------------------------------
IF ( @RET_1 = 0 ) BEGIN
IF ( @ISPARTS_1 = 1 ) BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_1
AND VALID = 1
AND ITEM_ID = @ITEM_ID_1

SET @C0_1 = 0

END ELSE IF ( @ISPARTS_1 = 3 ) BEGIN
UPDATE DBO.PANGYA_CADDIE_INFO
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_1
AND VALID = 1
AND ITEM_ID = @ITEM_ID_1

SET @C0_1 = 0

END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 - @C0_1
, VALID = CASE WHEN C0 <= @C0_1 THEN 0 ELSE 1 END
WHERE UID = @UID
AND TYPEID = @TYPEID_1
AND VALID = 1
AND ITEM_ID = @ITEM_ID_1
AND C0 >= @C0_1

IF (@C0_1_ORI >= @C0_1) BEGIN --CODE BY 김종열 2007 08 09
SET @C0_1 = @C0_1_ORI - @C0_1
END ELSE BEGIN
SET @C0_1 = 0
END

END
END

---- 2. CONSUME THE ITEM. ------------------------------------------------------------------------------
IF ( @RET_2 = 0 ) BEGIN
IF ( @ISPARTS_2 = 1 ) BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_2
AND VALID = 1
AND ITEM_ID = @ITEM_ID_2

SET @C0_2 = 0

END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 - @C0_2
, VALID = CASE WHEN C0 <= @C0_2 THEN 0 ELSE 1 END
WHERE UID = @UID
AND TYPEID = @TYPEID_2
AND VALID = 1
AND ITEM_ID = @ITEM_ID_2
AND C0 >= @C0_2

IF (@C0_2_ORI >= @C0_2) BEGIN --CODE BY 김종열 2007 08 09
SET @C0_2 = @C0_2_ORI - @C0_2
END ELSE BEGIN
SET @C0_2 = 0
END

END
END

---- 3. CONSUME THE ITEM. ------------------------------------------------------------------------------
IF ( @RET_3 = 0 ) BEGIN
IF ( @ISPARTS_3 = 1 ) BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_3
AND VALID = 1
AND ITEM_ID = @ITEM_ID_3

SET @C0_3 = 0

END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 - @C0_3
, VALID = CASE WHEN C0 <= @C0_3 THEN 0 ELSE 1 END
WHERE UID = @UID
AND TYPEID = @TYPEID_3
AND VALID = 1
AND ITEM_ID = @ITEM_ID_3
AND C0 >= @C0_3

IF (@C0_3_ORI >= @C0_3) BEGIN --CODE BY 김종열 2007 08 09
SET @C0_3 = @C0_3_ORI - @C0_3
END ELSE BEGIN
SET @C0_3 = 0
END
END
END

---- 4. CONSUME THE ITEM. ------------------------------------------------------------------------------
IF ( @RET_4 = 0 ) BEGIN
IF ( @ISPARTS_4 = 1 ) BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET VALID = 0
WHERE UID = @UID
AND TYPEID = @TYPEID_4
AND VALID = 1
AND ITEM_ID = @ITEM_ID_4

SET @C0_4 = 0
END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 - @C0_4
, VALID = CASE WHEN C0 <= @C0_4 THEN 0 ELSE 1 END
WHERE UID = @UID
AND TYPEID = @TYPEID_4
AND VALID = 1
AND ITEM_ID = @ITEM_ID_4
AND C0 >= @C0_4

IF (@C0_4_ORI >= @C0_4) BEGIN --CODE BY 김종열 2007 08 09
SET @C0_4 = @C0_4_ORI - @C0_4
END ELSE BEGIN
SET @C0_4 = 0
END
END
END

END ELSE BEGIN
SET @RET_0 = 1 -- 0. 소모 실패
END


IF (@RET_0 = 0 ) BEGIN -- 회수 성공시
IF ( @ISPARTS_0 IN (1, 2) ) BEGIN

-- 패키지 아이템의 경우 구성물 들을 모두 지급한다.
INSERT INTO DBO.PANGYA_ITEM_WAREHOUSE (UID,TYPEID,VALID,REGDATE,GIFT_FLAG,FLAG,APPLYTIME,ENDDATE,C0,C1,C2,C3,C4,PURCHASE,ITEMTYPE)
VALUES(@UID,@TYPEID_0,1,GETDATE(),0,1,GETDATE(),GETDATE(),0,0,0,0,0,0,0)

SET @ITEM_ID_0 = SCOPE_IDENTITY()
SET @C0_0 = 1

END ELSE IF ( @ISPARTS_0 = 3 ) BEGIN

-- 패키지 아이템의 경우 구성물 들을 모두 지급한다.
SET @CADDIECLEVEL = ISNULL(@CADDIECLEVEL , 0)
SET @CADDIEEXP = ISNULL(@CADDIEEXP , 0)

INSERT INTO DBO.PANGYA_CADDIE_INFO(UID,TYPEID,PARTS_TYPEID,GIFT_FLAG,PERIOD,ENDDATE,RENTFLAG,PURCHASE, CLEVEL ,[EXP] )
VALUES(@UID,@TYPEID_0,0,0,0, DATEADD(DAY,0,GETDATE()),0,0, @CADDIECLEVEL, @CADDIEEXP)

SET @ITEM_ID_0 = SCOPE_IDENTITY()
SET @C0_0 = 1

END ELSE BEGIN
IF (@ITEM_ID_0 = 0 ) BEGIN
INSERT INTO DBO.PANGYA_ITEM_WAREHOUSE(UID,TYPEID,VALID,REGDATE,GIFT_FLAG,FLAG,APPLYTIME,ENDDATE,C0,C1,C2,C3,C4,PURCHASE,ITEMTYPE)
VALUES(@UID,@TYPEID_0,1,GETDATE(),0,1,GETDATE(),GETDATE(),@C0_0,0,0,0,0,0,0)

SET @ITEM_ID_0 = SCOPE_IDENTITY()
END ELSE BEGIN
UPDATE DBO.PANGYA_ITEM_WAREHOUSE
SET C0 = C0 + @C0_0
WHERE UID = @UID
AND TYPEID = @TYPEID_0
AND VALID = 1
AND ITEM_ID = @ITEM_ID_0

SELECT TOP 1 @C0_0 = C0
FROM DBO.PANGYA_ITEM_WAREHOUSE
WHERE UID = @UID
AND TYPEID = @TYPEID_0
AND VALID = 1
AND ITEM_ID = @ITEM_ID_0
END
END

/***********카드에 쓰려고 **********/

--DECLARE @JOIN_ITEMID_NEW BIGINT
DECLARE @CARD_MAGICBOX TABLE (
IDX TINYINT IDENTITY(1,1)
, UID INT
, JOIN_ITEMID BIGINT
, JOIN_TYPEID INT
, CARD_TYPEID INT
, CARD_ITEMID BIGINT
, CARD_TYPE TINYINT
, SOKET_NUM TINYINT
, IN_DATE DATETIME )

DECLARE @JOIN_IDX INT

SELECT TOP 1 @JOIN_IDX = JOIN_IDX
FROM DBO.TD_CARD_JOIN2
WHERE UID = @UID AND JOIN_TYPEID = @TYPEID_1 AND JOIN_ITEMID = @ITEM_ID_1 AND END_DT IS NULL AND USE_YN = 'Y'

IF (@JOIN_IDX IS NOT NULL) BEGIN
UPDATE DBO.TD_CARD_JOIN2
SET UID = @UID
, JOIN_TYPEID = @TYPEID_0
, JOIN_ITEMID = @ITEM_ID_0
, USE_DT = GETDATE()
WHERE UID = @UID
AND JOIN_TYPEID = @TYPEID_1
AND JOIN_ITEMID = @ITEM_ID_1
AND USE_DT < GETDATE()
AND END_DT IS NULL
AND USE_YN = 'Y'
END
/*************************************/




SET @RET_0 = 0

END

ELSE BEGIN
SET @ITEM_ID_1 = 0
SET @TYPEID_1 = 0
SET @C0_1 = 0

SET @ITEM_ID_2 = 0
SET @TYPEID_2 = 0 SET @C0_2 = 0

SET @ITEM_ID_3 = 0
SET @TYPEID_3 = 0
SET @C0_3 = 0

SET @ITEM_ID_4 = 0
SET @TYPEID_4 = 0
SET @C0_4 = 0

SET @ITEM_ID_0 = 0
SET @TYPEID_0 = 0
SET @C0_0 = 0

SET @RET_0 = 1
END


--SELECT @RET_0 AS RET ,
-- @ITEM_ID_1 AS ITEM_ID_1 , @TYPEID_1 AS TYPEID_1 , @C0_1 AS C0_1 , @ISPARTS_1 AS ISPARTS_1 ,
-- @ITEM_ID_2 AS ITEM_ID_2 , @TYPEID_2 AS TYPEID_2 , @C0_2 AS C0_2 , @ISPARTS_2 AS ISPARTS_2 ,
-- @ITEM_ID_3 AS ITEM_ID_3 , @TYPEID_3 AS TYPEID_3 , @C0_3 AS C0_3 , @ISPARTS_3 AS ISPARTS_3 ,
-- @ITEM_ID_4 AS ITEM_ID_4 , @TYPEID_4 AS TYPEID_4 , @C0_4 AS C0_4 , @ISPARTS_4 AS ISPARTS_4 ,
-- @ITEM_ID_0 AS ITEM_ID_0 , @TYPEID_0 AS TYPEID_0 , @C0_0 AS C0_0 , @ISPARTS_0 AS ISPARTS_0
/*
IF @RET_1 = 0 BEGIN
SELECT 1 AS RET_1, @ITEM_ID_1 AS ITEM_ID_1 , @TYPEID_1 AS TYPEID_1 , @C0_1 AS C0_1 , @ISPARTS_1 AS ISPARTS_1
END
IF @RET_2 = 0 BEGIN
SELECT 2 AS RET_2, @ITEM_ID_2 AS ITEM_ID_2 , @TYPEID_2 AS TYPEID_2 , @C0_2 AS C0_2 , @ISPARTS_2 AS ISPARTS_2
END
IF @RET_3 = 0 BEGIN
SELECT 3 AS RET_3, @ITEM_ID_3 AS ITEM_ID_3 , @TYPEID_3 AS TYPEID_3 , @C0_3 AS C0_3 , @ISPARTS_3 AS ISPARTS_3
END
IF @RET_4 = 0 BEGIN
SELECT 4 AS RET_4, @ITEM_ID_4 AS ITEM_ID_4 , @TYPEID_4 AS TYPEID_4 , @C0_4 AS C0_4 , @ISPARTS_4 AS ISPARTS_4
END
IF @RET_0 = 0 BEGIN
SELECT 0 AS RET_0, @ITEM_ID_0 AS ITEM_ID_0 , @TYPEID_0 AS TYPEID_0 , @C0_0 AS C0_0 , @ISPARTS_0 AS ISPARTS_0
END
SELECT 1 AS SEQ_1, ISNULL(@ITEM_ID_1,0) AS ITEM_ID_1 , ISNULL(@TYPEID_1,0) AS TYPEID_1 , ISNULL(@C0_1,0) AS C0_1 , ISNULL(@ISPARTS_1,0) AS ISPARTS_1
SELECT 2 AS SEQ_2, ISNULL(@ITEM_ID_2,0) AS ITEM_ID_2 , ISNULL(@TYPEID_2,0) AS TYPEID_2 , ISNULL(@C0_2,0) AS C0_2 , ISNULL(@ISPARTS_2,0) AS ISPARTS_2
SELECT 3 AS SEQ_3, ISNULL(@ITEM_ID_3,0) AS ITEM_ID_3 , ISNULL(@TYPEID_3,0) AS TYPEID_3 , ISNULL(@C0_3,0) AS C0_3 , ISNULL(@ISPARTS_3,0) AS ISPARTS_3
SELECT 4 AS SEQ_4, ISNULL(@ITEM_ID_4,0) AS ITEM_ID_4 , ISNULL(@TYPEID_4,0) AS TYPEID_4 , ISNULL(@C0_4,0) AS C0_4 , ISNULL(@ISPARTS_4,0) AS ISPARTS_4
SELECT 0 AS SEQ_0, @ITEM_ID_0 AS ITEM_ID_0, @TYPEID_0 AS TYPEID_0 , @C0_0 AS C0_0 , @ISPARTS_0 AS ISPARTS_0
*/
IF @RET_0 = 0 BEGIN
SELECT 1
--SELECT @RET_0, @RET_1, @RET_2, @RET_3, @RET_4
--SELECT @UID, @ITEM_ID_1 AS ITEM_ID_1 , @TYPEID_1 AS TYPEID_1, @ITEM_ID_2 AS ITEM_ID_2 , @TYPEID_2 AS TYPEID_2, @ITEM_ID_3 AS ITEM_ID_3 , @TYPEID_3 AS TYPEID_3, @ITEM_ID_4 AS ITEM_ID_4 , @TYPEID_4 AS TYPEID_4
--SELECT @UID, @TYPEID_1 AS TYPEID, @ITEM_ID_1 AS ITEM_ID, @C0_1 AS C0, 0 , 0, 0
--SELECT @UID, @TYPEID_2 AS TYPEID, @ITEM_ID_2 AS ITEM_ID, @C0_2, 0 , 0
--SELECT 0, @ITEM_ID_2 AS ITEM_ID, @TYPEID_2 AS TYPEID_2
--SELECT @UID, ISNULL(@ITEM_ID_3,0) AS ITEM_ID , ISNULL(@TYPEID_3,0) AS TYPEID_3, @C0_3, 0, 0
--SELECT @UID, ISNULL(@ITEM_ID_4,0) AS ITEM_ID , ISNULL(@TYPEID_4,0) AS TYPEID_4, @C0_4, 0, 0
--SELECT 1, @ITEM_ID_0 AS ITEM_ID_0, @TYPEID_0 AS TYPEID_0
--SELECT 0 AS SEQ_0, @ITEM_ID_0 AS ITEM_ID_0, @TYPEID_0 AS TYPEID_0 , @C0_0 AS C0_0 , @ISPARTS_0 AS ISPARTS_0
--SELECT @UID,@TYPEID_1,@ITEM_ID_1, @C0_1, @ITEM_ID_2 , 0, 0, 0
--SELECT @UID, @TYPEID_1, @C0_1, @TYPEID_2, @C0_2, @TYPEID_3, @C0_3, @TYPEID_4, @C0_4, @TYPEID_0, @C0_0
SELECT @RET_0, @ITEM_ID_1, @TYPEID_1, @C0_1,@ITEM_ID_2 ,@TYPEID_2, @C0_2, @ITEM_ID_3, @TYPEID_3, @C0_3, @ITEM_ID_4, @TYPEID_4, @C0_4, @ITEM_ID_0, @TYPEID_0, @C0_0, 0, 0
--SELECT @RET_0, @ITEM_ID_1, @TYPEID_1, @C0_1,@ITEM_ID_2 ,@TYPEID_2, @C0_2, @ITEM_ID_3, @TYPEID_3, @C0_3, @ITEM_ID_4, @TYPEID_4, @C0_4, @ITEM_ID_0, @TYPEID_0, @C0_0, 0, GETDATE()
/*
ERR = 1
,SRC1_ITEMID = 0
,SRC1_TYPEID = 0
,SRC1_CNT = 0
,SRC2_ITEMID = 0
,SRC2_TYPEID = 0
,SRC2_CNT = 0
,SRC3_ITEMID = 0
,SRC3_TYPEID = 0
,SRC3_CNT = 0
,SRC4_ITEMID = 0
,SRC4_TYPEID = 0
,SRC4_CNT = 0
, M_TYPEID = 0
, M_ITEMID = 0
, PERIOD =0
, ENDDATE = 0*/
END
return @RET_0
-- LEAVE LOG
INSERT INTO PANGYA_RECYCLE_ITEM_LOG(UID,RET,TYPEID_1,C0_1,TYPEID_2,C0_2,TYPEID_3,C0_3,TYPEID_4,C0_4,TYPEID_0,C0_0,REGDATE)
VALUES (@UID,@RET_0,@TYPEID_1,@C0_1,@TYPEID_2,@C0_2,@TYPEID_3,@C0_3,@TYPEID_4,@C0_4,@TYPEID_0,@C0_0,GETDATE())

SET NOCOUNT OFF
END

[MentaL]

[bubbastic]

awesome, thx for sharing this.

[a22345875]

I found the gameserver_th_581.02_rel.exe can be use Cadie's Cauldron
Why the gameserver_th_581.06_rel.exe use the Cadie's Cauldron want USP_ITEM_RECYCLE ?

[chreadie]

What sp is working there?
Posted via Mobile Device

[bubbastic]

eh 581.02 doesn't change anything o.O
please post a screenshot if your cauldron works with the gameserver 581.02 lmao
I don't believe this change anything XD but well give us some proofs then lol

[Acardia]

eh 581.02 doesn't change anything o.O
please post a screenshot if your cauldron works with the gameserver 581.02 lmao
I don't believe this change anything XD but well give us some proofs then lol

i tried the 581.02 and i can say it does work but you must logout and back in to see the changes i changed the colors on 6 outfits with it

[a22345875]

i tried the 581.02 and i can say it does work but you must logout and back in to see the changes i changed the colors on 6 outfits with it

Yes......

[kiuubi]

Nice Job

[bubbastic]

i tried the 581.02 and i can say it does work but you must logout and back in to see the changes i changed the colors on 6 outfits with it

Without the USP_ITEM_RECYCLE ?

[jub7cs]

Msg 208, Level 16, State 6, Procedure USP_ITEM_RECYCLE, Line 545
Invalid object name 'dbo.USP_ITEM_RECYCLE'.

[Acardia]

Without the USP_ITEM_RECYCLE ?

yep without USP_ITEM_RECYCLE

[bubbastic]

can you show us this succesfull recycled item with logs or screenshot or something ?

[PHOENIXXXX]

Not working for the moment but nice try.

[chreadie]

Partially means its not working ^^

Its working in the sense that it doesnt rollback transactions which means if you relog you will see the exchange was made but the client will still show an error.

---------- Post added at 08:44 PM ---------- Previous post was at 08:43 PM ----------

Can someone pls post the server version where cadies cauldron is working?

[Acardia]

Partially means its not working ^^

Its working in the sense that it doesnt rollback transactions which means if you relog you will see the exchange was made but the client will still show an error.

---------- Post added at 08:44 PM ---------- Previous post was at 08:43 PM ----------

Can someone pls post the server version where cadies cauldron is working?

Game Server exe 581.02 its working but requires a relog for the item change

[chreadie]

does 581.02 report errors in log? what SP is it using? what does that SP return?

its nice to have a working function but it doesnt tell me much unless you tell me those things or even better, post the working SP.

[hvdaedalus]

great thanks for that one chreadie :)

[Acardia]

does 581.02 report errors in log? what SP is it using? what does that SP return?

its nice to have a working function but it doesnt tell me much unless you tell me those things or even better, post the working SP.

no errors in any log ive seen im thinking with the right hex editing on the exe it might work instantly instead of login then logoff

[Acardia]

Just a question couldnt we just comment out the need for the USP_ITEM_RECYCLE in any sql files and have recycle works cause that one exe doesnt show logs of errors or anything just relog and bam you have your items

@chreadie when you mean partially working do you mean everything works upon loutout and back in ? like all color changes etc

[chreadie]

ive only tested strength boosters and other simple items

[chiosin2]

i thought so.. LoL

[hvdaedalus]

Since this is still an issue, I'd like to mention something.

<RECYCLE><MINUS SEQ='1' TYPEID='402653192' ITEM_ID='375' C0='2' ISPARTS='0' /><MINUS SEQ='2' TYPEID='402653185' ITEM_ID='8' C0='1' ISPARTS='0' /><MINUS SEQ='3' TYPEID='402653189' ITEM_ID='5' C0='1' ISPARTS='0' /><PLUS SEQ='1' ITEM_ID='4' TYPEID='402653188' C0='1' ISPARTS='0' /></RECYCLE>

This is the simple production of 1 Power Potion. The Only Problem which is still persisting, is that there is the wrong return code. No I correct myself: There is no Return Code at all. Below is the reason shown why.

[N_RECYCLE] XML : <RECYCLE><MINUS SEQ='1' TYPEID='402653192' ITEM_ID='375' C0='2' ISPARTS='0' /><MINUS SEQ='2' TYPEID='402653185' ITEM_ID='8' C0='1' ISPARTS='0' /><MINUS SEQ='3' TYPEID='402653189' ITEM_ID='5' C0='1' ISPARTS='0' /><PLUS SEQ='1' ITEM_ID='4' TYPEID='402653188' C0='1' ISPARTS='0' /></RECYCLE>
[E_RECYCLE] RETURN : ERROR((null))
[E_RECYCLE] DB Commmand Return Failed

I cannot fix it, since I do not know the name/names of the columns which will lead to completion and the Error is pointing to null, means no error message. It's like the old UnrealED times, a window containing:

Titlebar: Error [X]
Content: (Red X-Icon) Unexpected Error
[OK-Button]

[malcomtidus]

It's like the old UnrealED times, a window containing:

Titlebar: Error [X]
Content: (Red X-Icon) Unexpected Error
[OK-Button]

Off-topic just real quick: Another UnrealEd developer, eh? For as popular as it was I don't see many other people who even know what that is! Awesome.

The absolute worst incarnation was the Deus Ex engine. It was absolutely painful having so much work disappear because the engine itself is/was just so unstable. I was saving every map project every 2 minutes or 10 actions, haha... :/

Okay, sorry for the off-topic. :)

[hvdaedalus]

Yeah, I know what you mean. Deus Ex was such a great game, but the engine itself was a crippled thing. Now B2T:

I have been debugging the gameserver, but it is a pain in the ass. I have been doing it for 2-3 hours now, but I couldn't solve it. Maybe I am not so good at asm after all. If anyone is better than me:

For Olly: Search in Memory for "dbo.USP_ITEM_RECYCLE" and mark the whole Hex-Stuff (Hex or ascii section, doesn't matter). Set a BP there (for Olly 1.x: Memory - On Access, for Olly 2.x: Break on everything (Read, Write, Execute)) and go through the jumps until you get to the sql returning section (have no address on that, you need to find out yourself) and find out why the gameserver is saying: ERROR ((null))