[SQL] td_char_equip_save_s4

[chreadie]

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

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

 

[kajornjorn]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

Thx It work.

 

[shadowsfx]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

this worked perfectly fine. thank you.

 

[bubbastic]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

@Chreadie : if you want the ring to be saved as well, you need to add auxparts in your script

But what is the point of reposting this procedure that mikumiku had already fixed ?

 

[kajornjorn]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

Script Not Save Character Nell.

 

[chreadie]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

@Chreadie : if you want the ring to be saved as well, you need to add auxparts in your script

But what is the point of reposting this procedure that mikumiku had already fixed ?

The sp that came with the database didnt work for me so i corrected the errors and when i got it working i posted it here, Sorry.

Ill fix the auxparts and not post it here?

 

[StarNet]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

The sp that came with the database didnt work for me so i corrected the errors and when i got it working i posted it here, Sorry.

Ill fix the auxparts and not post it here?

yeah for me it not worked too. but your fix work

what do you mean with not post the auxparts fix here :?:

 

[chreadie]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

According to bubbastic mikumiku already fixed this SP, so whats the point of me posting more fixes here?

 

[bubbastic]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

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)

 

[terrorofdeath]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

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)

Not to be patronizing or anything, but chreadie's procedure is the only one I've seen which works for me.

All the ones I have ever found, have been linked to on the forums, and the one you just posted failed each time I attempted to use them. :/:

 

[chreadie]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

Thanks for adding that bubbastic! Now we just have to add it to a release along with USP_MAIL_SEND and the 584 exe!

 

[bubbastic]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

All the ones I have ever found, have been linked to on the forums, and the one you just posted failed each time I attempted to use them. :/:

All procedures I got from here (noah's initial post) or that I posted myself worked perfectly with sql enterprise 2005 sp3 on XP pro SP3.
I also tested them on several editions like sql express 2005 and sql 2008 but I ran into problems sometimes with them so I guess the problem don't come from the procedure itself but from your server setup and/or your configuration. :ehh:

 

[chreadie]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

well im running SQL Server 2008 because of features and familiarity, i run alot of SQL Server 2000 / 2005 / 2008 at work and from experiences with features and bugs i'd say 2008 is coming on strong!

as i said earlier this isnt the place for such discussions and im sorry this topic was made if it was a new release of something old. Ill try to test everything i do on both engines.

 

[Sonict]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

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)

It doesn't work for me. The server spat out an error. :/

I did copy and paste "chreadie's" one from the first post (Yes, I know the rings weren't included) and it works fine.

 

[Tsukasa]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

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.

 

[StarNet]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

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]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

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]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

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]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

THX for your Job !!!!!

Cordially

Kyu

 

[Sonict]

Re: [Release] TD_CHAR_EQUIP_SAVE_S4

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...