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
