USE [Pangya_S4_TH]
GO
/***************************************************
* Pangya Server 4.9 Database Fixes *
* *
* Last Update: 2010-12-04 *
* *
* Run this after bootstrapping the database and *
* creating the tables through the .sql files from *
* the server-package. *
* *
* The following procedures were added/modified: *
* =============================================== *
* USP_NICKNAME_UPDATE by darkknight/advance *
* USP_TUTORIAL_GET_STATUS by bubbastic *
* USP_CARD_LIST by bubbastic *
* USP_CHAR_EQUIP_LOAD_S4 by bubbastic *
* USP_CARD_GET_LIST by bubbastic *
* Create table TD_CHAR_EQUIP_S4 by bubbastic *
* USP_CHAR_EQUIP_SAVE_S4 by bubbastic *
* USP_CHAR_EQUIP_UCC_S4 by tsukasa *
***************************************************/
/****** Object: StoredProcedure [dbo].[USP_NICKNAME_UPDATE] by darkknight/advance ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_NICKNAME_UPDATE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DECLARE @USP_NICKNAME_UPDATE_SQL VARCHAR(8000)
SET @USP_NICKNAME_UPDATE_SQL = N'
CREATE PROCEDURE [dbo].[USP_NICKNAME_UPDATE]
@IDX int,
@NICKNAME varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT 0
END'
EXEC(@USP_NICKNAME_UPDATE_SQL)
PRINT 'NOTE: StoredProcedure [dbo].[USP_NICKNAME_UPDATE] created.'
END
ELSE
PRINT 'NOTE: StoredProcedure [dbo].[USP_NICKNAME_UPDATE] already exists and will not be modified.'
/****** Object: StoredProcedure [dbo].[USP_TUTORIAL_GET_STATUS] by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_TUTORIAL_GET_STATUS]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DECLARE @USP_TUTORIAL_GET_STATUS_SQL VARCHAR(8000)
SET @USP_TUTORIAL_GET_STATUS_SQL = N'
CREATE PROCEDURE [dbo].[USP_TUTORIAL_GET_STATUS] (
@UID INT
)
AS
BEGIN
SET NOCOUNT ON
SELECT A.USER_LEVEL
,[QUEST] = SUM(A.STEP)
FROM DBO.TA_TUTORIAL_ACC A WITH (NOLOCK)
INNER JOIN
(
SELECT SEQ
FROM DBO.TD_TUTORIAL WITH (NOLOCK)
WHERE UID = @UID
GROUP BY SEQ
) B
ON A.SEQ = B.SEQ
GROUP BY A.USER_LEVEL
END'
EXEC(@USP_TUTORIAL_GET_STATUS_SQL)
PRINT 'NOTE: StoredProcedure [dbo].[USP_TUTORIAL_GET_STATUS] created.'
END
ELSE
PRINT 'NOTE: StoredProcedure [dbo].[USP_TUTORIAL_GET_STATUS] already exists and will not be modified.'
/****** Object: StoredProcedure [dbo].[USP_CARD_LIST] by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CARD_LIST]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DECLARE @USP_CARD_LIST_SQL VARCHAR(8000)
SET @USP_CARD_LIST_SQL = N'
CREATE PROC [dbo].[USP_CARD_LIST] (
@UID INT
)
AS
BEGIN
SET NOCOUNT ON
SELECT UID
, CARD_TYPEID
, CARD_ITEMID
, CARD_TYPE
, QTY
, IS_USE_DT = 0
FROM DBO.TD_CARD_USRS WITH (NOLOCK)
WHERE UID = @UID
AND USE_YN = ''Y''
END'
EXEC(@USP_CARD_LIST_SQL)
PRINT 'NOTE: StoredProcedure [dbo].[USP_CARD_LIST] created.'
END
ELSE
PRINT 'NOTE: StoredProcedure [dbo].[USP_CARD_LIST] already exists and will not be modified.'
/****** Object: StoredProcedure [dbo].[USP_CHAR_EQUIP_LOAD_S4] by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CHAR_EQUIP_LOAD_S4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DECLARE @USP_CHAR_EQUIP_LOAD_S4_SQL VARCHAR(8000)
SET @USP_CARD_LIST_SQL = N'
CREATE PROC [dbo].[USP_CHAR_EQUIP_LOAD_S4] (
@UID INT
, @TYPEID INT = 0
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
X.*
, UCCIDX = Y.UCCIDX
, SEQ = Y.SEQ
, ITEMID = Y.ITEMID
FROM
(SELECT
*
FROM
(
SELECT
A.UID
, A.ITEM_ID
, A.TYPEID
, EQUIP_NUM = CASE
WHEN B.SEQ = 1 THEN 1
WHEN B.SEQ = 2 THEN 2
WHEN B.SEQ = 3 THEN 3
WHEN B.SEQ = 4 THEN 4
WHEN B.SEQ = 5 THEN 5
WHEN B.SEQ = 6 THEN 6
WHEN B.SEQ = 7 THEN 7
WHEN B.SEQ = 8 THEN 8
WHEN B.SEQ = 9 THEN 9
WHEN B.SEQ = 10 THEN 10
WHEN B.SEQ = 11 THEN 11
WHEN B.SEQ = 12 THEN 12
WHEN B.SEQ = 13 THEN 13
WHEN B.SEQ = 14 THEN 14
WHEN B.SEQ = 15 THEN 15
WHEN B.SEQ = 16 THEN 16
WHEN B.SEQ = 17 THEN 17
WHEN B.SEQ = 18 THEN 18
WHEN B.SEQ = 19 THEN 19
WHEN B.SEQ = 20 THEN 20
WHEN B.SEQ = 21 THEN 21
WHEN B.SEQ = 22 THEN 22
WHEN B.SEQ = 23 THEN 23
WHEN B.SEQ = 24 THEN 24
WHEN B.SEQ = 25 THEN 25
WHEN B.SEQ = 26 THEN 26
WHEN B.SEQ = 27 THEN 27
WHEN B.SEQ = 28 THEN 28
WHEN B.SEQ = 29 THEN 29
WHEN B.SEQ = 30 THEN 30
WHEN B.SEQ = 31 THEN 31
WHEN B.SEQ = 32 THEN 32
WHEN B.SEQ = 33 THEN 33
WHEN B.SEQ = 34 THEN 34
WHEN B.SEQ = 35 THEN 35
WHEN B.SEQ = 36 THEN 36
WHEN B.SEQ = 37 THEN 37
WHEN B.SEQ = 38 THEN 38
ELSE NULL END
, VALUE = CASE
WHEN B.SEQ = 1 THEN A.PARTS_1
WHEN B.SEQ = 2 THEN A.PARTS_2
WHEN B.SEQ = 3 THEN A.PARTS_3
WHEN B.SEQ = 4 THEN A.PARTS_4
WHEN B.SEQ = 5 THEN A.PARTS_5
WHEN B.SEQ = 6 THEN A.PARTS_6
WHEN B.SEQ = 7 THEN A.PARTS_7
WHEN B.SEQ = 8 THEN A.PARTS_8
WHEN B.SEQ = 9 THEN A.PARTS_9
WHEN B.SEQ = 10 THEN A.PARTS_10
WHEN B.SEQ = 11 THEN A.PARTS_11
WHEN B.SEQ = 12 THEN A.PARTS_12
WHEN B.SEQ = 13 THEN A.PARTS_13
WHEN B.SEQ = 14 THEN A.PARTS_14
WHEN B.SEQ = 15 THEN A.PARTS_15
WHEN B.SEQ = 16 THEN A.PARTS_16
WHEN B.SEQ = 17 THEN A.PARTS_17
WHEN B.SEQ = 18 THEN A.PARTS_18
WHEN B.SEQ = 19 THEN A.PARTS_19
WHEN B.SEQ = 20 THEN A.PARTS_20
WHEN B.SEQ = 21 THEN A.PARTS_21
WHEN B.SEQ = 22 THEN A.PARTS_22
WHEN B.SEQ = 23 THEN A.PARTS_23
WHEN B.SEQ = 24 THEN A.PARTS_24
WHEN B.SEQ = 25 THEN A.AUXPARTS_1
WHEN B.SEQ = 26 THEN A.AUXPARTS_2
WHEN B.SEQ = 27 THEN A.AUXPARTS_3
WHEN B.SEQ = 28 THEN A.AUXPARTS_4
WHEN B.SEQ = 29 THEN A.AUXPARTS_5
WHEN B.SEQ = 30 THEN A.DEFAULT_HAIR
WHEN B.SEQ = 31 THEN A.DEFAULT_SHIRTS
WHEN B.SEQ = 32 THEN A.GIFT_FLAG
WHEN B.SEQ = 33 THEN A.PCL0
WHEN B.SEQ = 34 THEN A.PCL1
WHEN B.SEQ = 35 THEN A.PCL2
WHEN B.SEQ = 36 THEN A.PCL3
WHEN B.SEQ = 37 THEN A.PCL4
WHEN B.SEQ = 38 THEN A.PURCHASE
ELSE NULL END
FROM
DBO.PANGYA_CHARACTER_INFORMATION A (NOLOCK)
CROSS JOIN
(SELECT SEQ FROM TZ_DUMMY WHERE SEQ BETWEEN 1 AND 50) B
WHERE
A.UID = @UID
AND A.TYPEID = CASE WHEN (@TYPEID = 0) THEN A.TYPEID ELSE @TYPEID END
) C
WHERE
EQUIP_NUM IS NOT NULL
)X
LEFT OUTER JOIN
(
SELECT
B.UID
, A.TYPEID
, A.UCCIDX
, A.SEQ
, B.CHARACTER_ITEMID
, B.EQUIP_NUM
, B.EQUIP_TYPE
, B.ITEMID
FROM
(
SELECT UID, TYPEID, UCCIDX, SEQ, ITEM_ID--, CHARACTER_ITEM_ID, EQUIP_NUM, EQUIP_TYPE
FROM DBO.TU_UCC
WHERE UID = @UID-- AND USE_YN = ''Y''
) A
RIGHT OUTER JOIN
(
SELECT UID, CHARACTER_ITEMID, EQUIP_NUM, EQUIP_TYPE, ITEMID
FROM DBO.TD_CHAR_EQUIP
WHERE UID = @UID AND USE_YN = ''Y''
) B
ON A.UID = B.UID
AND A.ITEM_ID = B.ITEMID
--AND A.EQUIP_NUM = B.EQUIP_NUM
) Y
ON X.ITEM_ID = Y.CHARACTER_ITEMID
AND X.EQUIP_NUM = Y.EQUIP_NUM
ORDER BY X.ITEM_ID, X.EQUIP_NUM
SET NOCOUNT OFF
END'
EXEC(@USP_CHAR_EQUIP_LOAD_S4_SQL)
PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_LOAD_S4] created.'
END
ELSE
PRINT 'NOTE: [dbo].[USP_CHAR_EQUIP_LOAD_S4] already exists and will not be modified.'
/****** Object: StoredProcedure [dbo].[USP_CARD_GET_LIST] by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CARD_GET_LIST]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DECLARE @USP_CARD_GET_LIST_SQL VARCHAR(8000)
SET @USP_CARD_GET_LIST_SQL = N'
CREATE PROC [dbo].[USP_CARD_GET_LIST] (
@UID INT
)
AS
BEGIN
SET NOCOUNT ON
SELECT CARD_TYPEID
, CARD_ITEMID
, QTY
, IS_USE_DT = 0
FROM DBO.TD_CARD_USRS WITH (NOLOCK)
WHERE UID = @UID
AND USE_YN = ''Y''
END'
EXEC(@USP_CARD_GET_LIST_SQL)
PRINT 'NOTE: StoredProcedure [dbo].[USP_CARD_GET_LIST] created.'
END
ELSE
PRINT 'NOTE: StoredProcedure [dbo].[USP_CARD_GET_LIST] already exists and will not be modified.'
/****** Object: Table [dbo].[TD_CHAR_EQUIP_S4] by bubbastic ******/
IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = N'TD_CHAR_EQUIP_S4')
BEGIN
DECLARE @ADD_TD_CHAR_EQUIP_S4_SQL VARCHAR(8000)
SET @ADD_TD_CHAR_EQUIP_S4_SQL = N'
SET ANSI_PADDING ON;
CREATE TABLE [dbo].[TD_CHAR_EQUIP_S4](
[SEQ] [int] IDENTITY(1,1) NOT NULL,
[UID] [int] NOT NULL,
[CHAR_ITEMID] [int] NULL,
[ITEMID] [bigint] NULL,
[TYPEID] [int] NULL,
[EQUIP_NUM] [tinyint] NULL,
[UCC_IDX] [char](8) NULL,
[USE_YN] [char](1) NULL
) ON [PRIMARY];
SET ANSI_PADDING OFF;'
EXEC(@ADD_TD_CHAR_EQUIP_S4_SQL)
PRINT 'NOTE: Table [dbo].[TD_CHAR_EQUIP_S4] created.'
END
ELSE
PRINT 'NOTE: Table [dbo].[TD_CHAR_EQUIP_S4] already exists and will not be modified.'
/****** Object: StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4] by bubbastic ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CHAR_EQUIP_SAVE_S4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DECLARE @USP_CHAR_EQUIP_SAVE_S4_PROC_SQL VARCHAR(8000)
SET @USP_CHAR_EQUIP_SAVE_S4_PROC_SQL = N'
CREATE 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
/*****************************************
카드 장착정보 ITEM 추가: SELECT * FROM TD_CARD_EQUIP
했다고 주석에 나와있지만 무슨소린지 아무도 모른다..
뭘 어쨌다는건지....
알게 되면 주석을 추가해 주세요 ㅡ.ㅡ
******************************************/
UPDATE DBO.TD_CHAR_EQUIP_S4
SET USE_YN = ''N''
WHERE UID = @UID
INSERT INTO DBO.TD_CHAR_EQUIP_S4 ( UID, CHAR_ITEMID, ITEMID, TYPEID, EQUIP_NUM, UCC_IDX, USE_YN )
SELECT
@UID
, @CHAR_ITEM_ID
, CASE ITEMID WHEN 0 THEN 0 ELSE ITEMID END
, TYPEID
, B.EQUIP_NUM
, UCCIDX
, ''Y''
FROM (
SELECT *
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'
EXEC(@USP_CHAR_EQUIP_SAVE_S4_PROC_SQL)
PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4] created.'
END
ELSE
PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4] already exists and will not be modified.'
/****** Object: StoredProcedure [dbo].[USP_CHAR_EQUIP_UCC_S4] by tsukasa ******/
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[USP_CHAR_EQUIP_UCC_S4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DECLARE @USP_CHAR_EQUIP_UCC_S4_SQL VARCHAR(8000)
SET @USP_CHAR_EQUIP_UCC_S4_SQL = N'
CREATE PROC [dbo].[USP_CHAR_EQUIP_UCC_S4] (
@UID INT
) -- WITH ENCRYPTION
AS
/***********************
DESCRIPTION : GET CHARINFO OF USER EQUIP CHARACTER
유저가 장착한 캐릭터의 정보를 가져온다.
한국. 시즌4. 친구초대
OBJECT NAME : USP_CHAR_USER_EQUIP
CREATED BY : JAE WOONG LEE
LAST UPDATE : 2008-02-04
RETURN VALUES : SEQ(TINYINT 0:NONE)
EXCUTE : EXEC DBO.USP_EVENT_SEQ_S 123, 1
***********************/
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @CHAR_TYPEID INT
SELECT
@CHAR_TYPEID = TYPEID
FROM
DBO.PANGYA_USER_EQUIP A (NOLOCK)
INNER JOIN
DBO.PANGYA_CHARACTER_INFORMATION B (NOLOCK)
ON A.UID = B.UID
AND A.CHARACTER_ID = B.ITEM_ID
WHERE
A.UID = @UID
IF (@CHAR_TYPEID IS NOT NULL) BEGIN
EXEC DBO.USP_CHAR_EQUIP_LOAD @UID, @CHAR_TYPEID
END
SET NOCOUNT OFF
END'
EXEC(@USP_CHAR_EQUIP_UCC_S4_SQL)
PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_UCC_S4] created.'
END
ELSE
PRINT 'NOTE: StoredProcedure [dbo].[USP_CHAR_EQUIP_UCC_S4] already exists and will not be modified.'
GO
