็Auto Aux[Ring] not work

[thanakhornza]

I do not want to wear it again, this time when login game.
please helpme

 

[CCasusensa]

Please check ur sql
Char_All_equip_Save_S4

 

[thanakhornza]

i use
http://forum.ragezone.com/f513/sql-td_char_equip_save_s4-720758/

and old sql is

USE [Pangya_S4_TH]
GO
/****** Object: StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4] Script Date: 03/18/2015 21:37:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER PROC [dbo].[USP_CHAR_EQUIP_SAVE_S4] (
@UID INT
, Char_ITEM_ID INT
, @STR VARCHAR(8000)
, @DEBUG TINYINT = 0
)
AS


BEGIN


-- '1^123456|2|3^1243^2214^1'
-- eQuIP_NUM^@TYPEID^@ITEMID^@UCCIDX^@SEQ|
-- eQuIP_NUM^@TYPEID^@ITEMID^@UCCIDX^@SEQ




SET NOCOUNT ON


DECLARE ret INT
SET ret = 0




--INPUT ??(1? ?? ??)
IF OBJECT_ID('TEMPDB..#TD_SRC ') IS NOT NULL
DROP TABLE #TD_SRC


CREATE TABLE #TD_SRC (
IDX SMALLINT IDENTITY(1,1)
, DT VARCHAR(1000)
)




--INPUT ??(2? ??) ? RESULT? ??
--IF OBJECT_ID('TEMPDB..#TD_CHAR ') IS NOT NULL
--DROP TABLE #TD_CHAR


CREATE TABLE #TD_CHAR (
IDX SMALLINT IDENTITY(1,1)
, UID INT
, CHAR_ITEMID INT
, EQUIP_NUM INT -- ??? ?? ??
, TYPEID INT -- ???
, ITEMID INT -- ITEMID
, UCCIDX VARCHAR(8) -- UCC???
, SEQ SMALLINT -- UCC ???
)




DECLARE SQL VARCHAR(8000)
, @STR_SEP CHAR(1)
, IntLOOP INT


SET @STR_SEP = '|'
SET SQL = ''
SET IntLOOP = 1




IF CHARINDEX('|', @STR) > 0 BEGIN
SELECT SQL= 'SELECT LTRIM(C) C FROM (SELECT '''
+ REPLACE(CONT,@STR_SEP,''' C UNION ALL ' + CHAR(13) + 'SELECT ''') + ''') T'
FROM ( SELECT CONT = @STR ) X
END ELSE BEGIN
SET SQL = 'SELECT LTRIM(C) C FROM (SELECT ''' + @STR + ''' C) T'
END


INSERT INTO #TD_SRC (DT)
EXEC SQL)




DECLARE eQuIP_NUM VARCHAR(20)
, @TYPEID VARCHAR(20)
, @ITEMID VARCHAR(20)
, @UCCIDX VARCHAR(20)
, @SEQ VARCHAR(20)




WHILE EXISTS( SELECT 1 FROM #TD_SRC WHERE IDX = IntLOOP ) BEGIN
SELECT SQL = REPLACE(DT, '^', ' ^') FROM #TD_SRC WHERE IDX = IntLOOP


EXEC MASTER..XP_SSCANF SQL, '%s ^%s ^%s ^%s ^%s', eQuIP_NUM OUTPUT, @TYPEID OUTPUT, @ITEMID OUTPUT, @UCCIDX OUTPUT, @SEQ OUTPUT


INSERT INTO #TD_CHAR (UID, CHAR_ITEMID, EQUIP_NUM, TYPEID, ITEMID, UCCIDX, SEQ)
SELECT @UID
, Char_ITEM_ID
, EQUIP_NUM = CAST eQuIP_NUM AS INT)
, TYPEID = CAST(@TYPEID AS INT)
, ITEMID = CAST(@ITEMID AS INT)
, UCCIDX = @UCCIDX
, SEQ = CAST(@SEQ AS INT)


SET IntLOOP = IntLOOP + 1


END


CREATE CLUSTERED INDEX CIDX_TD_CHAR
ON #TD_CHAR (EQUIP_NUM)










-- ??? ?? ?? UPDATE? ?? => !!! AD-HOC ???? ? ???!!!


BEGIN TRY


BEGIN TRAN


UPDATE B
SET
parts_1 = CASE WHEN A.parts_1 > 0 THEN A.parts_1 ELSE 0 END
, parts_2 = CASE WHEN A.parts_2 > 0 THEN A.parts_2 ELSE 0 END
, parts_3 = CASE WHEN A.parts_3 > 0 THEN A.parts_3 ELSE 0 END
, parts_4 = CASE WHEN A.parts_4 > 0 THEN A.parts_4 ELSE 0 END
, parts_5 = CASE WHEN A.parts_5 > 0 THEN A.parts_5 ELSE 0 END
, parts_6 = CASE WHEN A.parts_6 > 0 THEN A.parts_6 ELSE 0 END
, parts_7 = CASE WHEN A.parts_7 > 0 THEN A.parts_7 ELSE 0 END
, parts_8 = CASE WHEN A.parts_8 > 0 THEN A.parts_8 ELSE 0 END
, parts_9 = CASE WHEN A.parts_9 > 0 THEN A.parts_9 ELSE 0 END
, parts_10 = CASE WHEN A.parts_10 > 0 THEN A.parts_10 ELSE 0 END
, parts_11 = CASE WHEN A.parts_11 > 0 THEN A.parts_11 ELSE 0 END
, parts_12 = CASE WHEN A.parts_12 > 0 THEN A.parts_12 ELSE 0 END
, parts_13 = CASE WHEN A.parts_13 > 0 THEN A.parts_13 ELSE 0 END
, parts_14 = CASE WHEN A.parts_14 > 0 THEN A.parts_14 ELSE 0 END
, parts_15 = CASE WHEN A.parts_15 > 0 THEN A.parts_15 ELSE 0 END
, parts_16 = CASE WHEN A.parts_16 > 0 THEN A.parts_16 ELSE 0 END
, parts_17 = CASE WHEN A.parts_17 > 0 THEN A.parts_17 ELSE 0 END
, parts_18 = CASE WHEN A.parts_18 > 0 THEN A.parts_18 ELSE 0 END
, parts_19 = CASE WHEN A.parts_19 > 0 THEN A.parts_19 ELSE 0 END
, parts_20 = CASE WHEN A.parts_20 > 0 THEN A.parts_20 ELSE 0 END
, parts_21 = CASE WHEN A.parts_21 > 0 THEN A.parts_21 ELSE 0 END
, parts_22 = CASE WHEN A.parts_22 > 0 THEN A.parts_22 ELSE 0 END
, parts_23 = CASE WHEN A.parts_23 > 0 THEN A.parts_23 ELSE 0 END
, parts_24 = CASE WHEN A.parts_24 > 0 THEN A.parts_24 ELSE 0 END


FROM
(
SELECT UID
, CHAR_ITEMID = MAX(CHAR_ITEMID)
, parts_1 = MAX(CASE WHEN EQUIP_NUM = 1 THEN TYPEID ELSE 0 END)
, parts_2 = MAX(CASE WHEN EQUIP_NUM = 2 THEN TYPEID ELSE 0 END)
, parts_3 = MAX(CASE WHEN EQUIP_NUM = 3 THEN TYPEID ELSE 0 END)
, parts_4 = MAX(CASE WHEN EQUIP_NUM = 4 THEN TYPEID ELSE 0 END)
, parts_5 = MAX(CASE WHEN EQUIP_NUM = 5 THEN TYPEID ELSE 0 END)
, parts_6 = MAX(CASE WHEN EQUIP_NUM = 6 THEN TYPEID ELSE 0 END)
, parts_7 = MAX(CASE WHEN EQUIP_NUM = 7 THEN TYPEID ELSE 0 END)
, parts_8 = MAX(CASE WHEN EQUIP_NUM = 8 THEN TYPEID ELSE 0 END)
, parts_9 = MAX(CASE WHEN EQUIP_NUM = 9 THEN TYPEID ELSE 0 END)
, parts_10 = MAX(CASE WHEN EQUIP_NUM = 10 THEN TYPEID ELSE 0 END)
, parts_11 = MAX(CASE WHEN EQUIP_NUM = 11 THEN TYPEID ELSE 0 END)
, parts_12 = MAX(CASE WHEN EQUIP_NUM = 12 THEN TYPEID ELSE 0 END)
, parts_13 = MAX(CASE WHEN EQUIP_NUM = 13 THEN TYPEID ELSE 0 END)
, parts_14 = MAX(CASE WHEN EQUIP_NUM = 14 THEN TYPEID ELSE 0 END)
, parts_15 = MAX(CASE WHEN EQUIP_NUM = 15 THEN TYPEID ELSE 0 END)
, parts_16 = MAX(CASE WHEN EQUIP_NUM = 16 THEN TYPEID ELSE 0 END)
, parts_17 = MAX(CASE WHEN EQUIP_NUM = 17 THEN TYPEID ELSE 0 END)
, parts_18 = MAX(CASE WHEN EQUIP_NUM = 18 THEN TYPEID ELSE 0 END)
, parts_19 = MAX(CASE WHEN EQUIP_NUM = 19 THEN TYPEID ELSE 0 END)
, parts_20 = MAX(CASE WHEN EQUIP_NUM = 20 THEN TYPEID ELSE 0 END)
, parts_21 = MAX(CASE WHEN EQUIP_NUM = 21 THEN TYPEID ELSE 0 END)
, parts_22 = MAX(CASE WHEN EQUIP_NUM = 22 THEN TYPEID ELSE 0 END)
, parts_23 = MAX(CASE WHEN EQUIP_NUM = 23 THEN TYPEID ELSE 0 END)
, parts_24 = MAX(CASE WHEN EQUIP_NUM = 24 THEN TYPEID ELSE 0 END)
FROM #TD_CHAR
GROUP BY UID
) A
INNER JOIN
(
SELECT *
FROM pangya_character_information WITH (NOLOCK)
WHERE UID = @UID
AND ITEM_ID = Char_ITEM_ID
) B
ON A.UID = B.UID
AND A.CHAR_ITEMID = B.ITEM_ID


IF ( @DEBUG = 1 ) BEGIN
SELECT *
FROM #TD_CHAR
END






/*****************************************


?? ???? ITEM ??: SELECT * FROM TD_CARD_EQUIP
??? ??? ????? ????? ??? ???..
? ??????....


?? ?? ??? ??? ??? ?.?


******************************************/


UPDATE DBO.TD_CHAR_EQUIP_S4
SET USE_YN = 'N'
WHERE UID = @UID
AND CHAR_ITEMID = Char_ITEM_ID




INSERT INTO DBO.TD_CHAR_EQUIP_S4 ( [UID], CHAR_ITEMID, ITEMID, TYPEID, EQUIP_NUM, USE_YN, IN_DATE )
SELECT
B.[UID]
, B.CHAR_ITEMID
, CASE B.ITEMID WHEN 0 THEN 0 ELSE B.ITEMID END
, B.TYPEID
, B.EQUIP_NUM
, 'Y'
, GETDATE()
FROM (
SELECT [UID], CHAR_ITEMID, ITEMID, TYPEID, EQUIP_NUM, 'Y' AS USE_YN
FROM #TD_CHAR
WHERE ITEMID IS NOT NULL
) B


COMMIT TRAN
SELECT [RET] = @@ERROR




IF ( @DEBUG = 1 ) BEGIN
SELECT *
FROM #TD_CHAR
END




END TRY
BEGIN CATCH


ROLLBACK TRAN
SELECT [RET] = 1


END CATCH




END

 

[CCasusensa]

in , parts_24 = CASE WHEN A.parts_24 > 0 THEN A.parts_24 ELSE 0 END
bottom add
, auxparts_1 = CASE WHEN A.auxparts_1 > 0 THEN A.auxparts_1 ELSE 0 END
, auxparts_2 = CASE WHEN A.auxparts_2 > 0 THEN A.auxparts_2 ELSE 0 END
, auxparts_3 = CASE WHEN A.auxparts_3 > 0 THEN A.auxparts_3 ELSE 0 END
, auxparts_4 = CASE WHEN A.auxparts_4 > 0 THEN A.auxparts_4 ELSE 0 END
, auxparts_5 = CASE WHEN A.auxparts_5 > 0 THEN A.auxparts_5 ELSE 0 END

then

in , parts_24 = MAX(CASE WHEN EQUIP_NUM = 24 THEN TYPEID ELSE 0 END)
bottom add
, auxparts_1 = MAX(CASE WHEN EQUIP_NUM = 25 THEN TYPEID ELSE 0 END)
, auxparts_2 = MAX(CASE WHEN EQUIP_NUM = 26 THEN TYPEID ELSE 0 END)
, auxparts_3 = MAX(CASE WHEN EQUIP_NUM = 27 THEN TYPEID ELSE 0 END)
, auxparts_4 = MAX(CASE WHEN EQUIP_NUM = 28 THEN TYPEID ELSE 0 END)
, auxparts_5 = MAX(CASE WHEN EQUIP_NUM = 29 THEN TYPEID ELSE 0 END)



save run that sql again

 

[thanakhornza]

oh , it working thank you very much