[SQL] td_char_equip_save_s4

[StarNet]

SQL Server 2008, manual setup and mikumiku's procedure on my end. Works flawlessly.

Repack acting up again?

Doesn't really matter anyway, as long as it works for you guys.

the problem is there's no UCCIDX inside TD_CHAR_EQUIP_S4 table in the repack, but if you manually add it to the table, bubbastic's proc will work.

[chreadie]

Whats it used for? does it corelate to TU_UCC? AHA! this might have something to do with the sending items with mail thing.

Ill look into it!

[Tsukasa]

It has absolutely nothing to do with item mailing.

The UCCIDX indicates what custom design file to load from the UCC backend. The filename is left in the database, behind an every increasing index.

[kiuubi]

THX for your Job !!!!!

Cordially

Kyu

[Sonict]

the problem is there's no UCCIDX inside TD_CHAR_EQUIP_S4 table in the repack, but if you manually add it to the table, bubbastic's proc will work.

The name of the header was easy enough. I think I screwed up on the value type though. What is it suppose to be? It looked like varchar(8), but I guess that was wrong...

[chreadie]

my guess is that its an INT or BIGINT since its an ID, check other tables for references to that column and set the same type as them

[Acardia]

People should learn how to use search button XD.

It's nice from you. Good intention I mean but you won't help them by spoonfeeding them. Most of people won't take time to look at what has already been fixed and they ask for help.

Anyway here is the most complete version of this procedure with rings saved as well. (Not posted yet anywhere)

Yours seems to return some errors upon logout on my sql server 2008

heres one i edited

USE [Pangya_S4_TH]
GO
/****** Im not sure everyone needs this but i didnt get the other fix to work, here is a SP that works. ******/
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
, 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

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)
, 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)
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

[StarNet]

Yours seems to return some errors upon logout on my sql server 2008

heres one i edited

you're not learning very good...
read all the posts here for the answer

[ffxforever]

why i cant connect to pyserver after executed the query?
i just copy then execute the query, did i do anything wrong?
sorry im a rookie here

[win98fif09]

works

[Sonict]

I have given up on trying to get this working. I did try and change stuff, but it wasn't working for me. :/

[korn4564]

Thx It Nice

[lg560852]

Excellent!It works on Sql 2005,too.

[rolovely]

thank you

[tiezan]

Whow, good think i search it before posting in new thread