[Download] Procedures Inexistentes / FIX

[HISORI]

Olá a todos !

Bom para um melhor entendimento e facil procura dos procedimentos que estão faltando em algumas tables, segue abaixo todas que estão disponiveis no momento:

Para quem não tem altere onde tiver ALTER para CREATE e execute.

USP_CARD_LIST

USE [Pangya_S4_TH]
GO
/****** StoredProcedure [dbo].[USP_CARD_LIST] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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

CHAR_EQUIP_LOAD_S4 <<< |Query que resolve o Problema de não aparecer os canais do Servidor/Salas

USE [Pangya_S4_TH]
GO
/****** Objet :  StoredProcedure [dbo].[USP_CHAR_EQUIP_LOAD_S4]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[USP_CHAR_EQUIP_LOAD_S4] (       
      @UID            INT     
)    
AS      
  
BEGIN   
    
SET NOCOUNT ON       

    IF (  @UID > 0 ) BEGIN

        SELECT    X.ITEM_ID      
                        , TYPEID 
                        /* new */ 
                        , ITEMID_PARTS_1      
                        , ITEMID_PARTS_2      
                        , ITEMID_PARTS_3      
                        , ITEMID_PARTS_4      
                        , ITEMID_PARTS_5      
                        , ITEMID_PARTS_6      
                        , ITEMID_PARTS_7      
                        , ITEMID_PARTS_8      
                        , ITEMID_PARTS_9      
                        , ITEMID_PARTS_10      
                        , ITEMID_PARTS_11      
                        , ITEMID_PARTS_12      
                        , ITEMID_PARTS_13      
                        , ITEMID_PARTS_14      
                        , ITEMID_PARTS_15      
                        , ITEMID_PARTS_16      
                        , ITEMID_PARTS_17      
                        , ITEMID_PARTS_18      
                        , ITEMID_PARTS_19      
                        , ITEMID_PARTS_20      
                        , ITEMID_PARTS_21      
                        , ITEMID_PARTS_22      
                        , ITEMID_PARTS_23      
                        , ITEMID_PARTS_24
                        /* new */ 
                        , PARTS_1      
                        , PARTS_2      
                        , PARTS_3      
                        , PARTS_4      
                        , PARTS_5      
                        , PARTS_6      
                        , PARTS_7      
                        , PARTS_8      
                        , PARTS_9      
                        , PARTS_10      
                        , PARTS_11      
                        , PARTS_12      
                        , PARTS_13      
                        , PARTS_14      
                        , PARTS_15      
                        , PARTS_16      
                        , PARTS_17      
                        , PARTS_18      
                        , PARTS_19      
                        , PARTS_20      
                        , PARTS_21      
                        , PARTS_22      
                        , PARTS_23      
                        , PARTS_24      
                        , AUXPARTS_1      
                        , AUXPARTS_2      
                        , AUXPARTS_3      
                        , AUXPARTS_4      
                        , AUXPARTS_5      
                        , DEFAULT_HAIR      
                        , DEFAULT_SHIRTS      
                        , GIFT_FLAG      
                        , PCL0      
                        , PCL1      
                        , PCL2      
                        , PCL3      
                        , PCL4      
                        , PURCHASE
        FROM    (    
                    SELECT      
                              ITEM_ID      
                            , TYPEID 
                            , PARTS_1      
                            , PARTS_2      
                            , PARTS_3      
                            , PARTS_4      
                            , PARTS_5      
                            , PARTS_6      
                            , PARTS_7      
                            , PARTS_8      
                            , PARTS_9      
                            , PARTS_10      
                            , PARTS_11      
                            , PARTS_12      
                            , PARTS_13      
                            , PARTS_14      
                            , PARTS_15      
                            , PARTS_16      
                            , PARTS_17      
                            , PARTS_18      
                            , PARTS_19      
                            , PARTS_20      
                            , PARTS_21      
                            , PARTS_22      
                            , PARTS_23      
                            , PARTS_24      
                            , AUXPARTS_1      
                            , AUXPARTS_2      
                            , AUXPARTS_3      
                            , AUXPARTS_4      
                            , AUXPARTS_5      
                            , DEFAULT_HAIR      
                            , DEFAULT_SHIRTS      
                            , GIFT_FLAG      
                            , PCL0      
                            , PCL1      
                            , PCL2      
                            , PCL3      
                            , PCL4      
                            , PURCHASE      
                    FROM    DBO.PANGYA_CHARACTER_INFORMATION WITH (NOLOCK)
                    WHERE    UID = @UID    
                ) X
                INNER JOIN
                ( 
                    SELECT    
                            ITEM_ID
                            , ITEMID_PARTS_1 = MAX(CASE WHEN EQUIP_NUM =  1 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_2 = MAX(CASE WHEN EQUIP_NUM =  2 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_3 = MAX(CASE WHEN EQUIP_NUM =  3 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_4 = MAX(CASE WHEN EQUIP_NUM =  4 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_5 = MAX(CASE WHEN EQUIP_NUM =  5 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_6 = MAX(CASE WHEN EQUIP_NUM =  6 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_7 = MAX(CASE WHEN EQUIP_NUM =  7 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_8 = MAX(CASE WHEN EQUIP_NUM =  8 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_9 = MAX(CASE WHEN EQUIP_NUM =  9 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_10 = MAX(CASE WHEN EQUIP_NUM =  10 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_11 = MAX(CASE WHEN EQUIP_NUM =  11 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_12 = MAX(CASE WHEN EQUIP_NUM =  12 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_13 = MAX(CASE WHEN EQUIP_NUM =  13 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_14 = MAX(CASE WHEN EQUIP_NUM =  14 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_15 = MAX(CASE WHEN EQUIP_NUM =  15 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_16 = MAX(CASE WHEN EQUIP_NUM =  16 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_17 = MAX(CASE WHEN EQUIP_NUM =  17 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_18 = MAX(CASE WHEN EQUIP_NUM =  18 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_19 = MAX(CASE WHEN EQUIP_NUM =  19 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_20 = MAX(CASE WHEN EQUIP_NUM =  20 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_21 = MAX(CASE WHEN EQUIP_NUM =  21 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_22 = MAX(CASE WHEN EQUIP_NUM =  22 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_23 = MAX(CASE WHEN EQUIP_NUM =  23 THEN ITEMID ELSE 0 END) 
                            , ITEMID_PARTS_24 = MAX(CASE WHEN EQUIP_NUM =  24 THEN ITEMID ELSE 0 END) 

                    FROM    (
                                SELECT      
                                          ITEM_ID      
                                        , TYPEID  
                                        , PARTS_1      
                                        , PARTS_2      
                                        , PARTS_3      
                                        , PARTS_4      
                                        , PARTS_5      
                                        , PARTS_6      
                                        , PARTS_7      
                                        , PARTS_8      
                                        , PARTS_9      
                                        , PARTS_10      
                                        , PARTS_11      
                                        , PARTS_12      
                                        , PARTS_13      
                                        , PARTS_14      
                                        , PARTS_15      
                                        , PARTS_16      
                                        , PARTS_17      
                                        , PARTS_18      
                                        , PARTS_19      
                                        , PARTS_20      
                                        , PARTS_21      
                                        , PARTS_22      
                                        , PARTS_23      
                                        , PARTS_24            
                                FROM    DBO.PANGYA_CHARACTER_INFORMATION WITH (NOLOCK)                      
                                WHERE    UID = @UID
                            ) A
                            LEFT OUTER JOIN
                            (
                                SELECT    CHAR_ITEMID, ITEMID, TYPEID, EQUIP_NUM
                                FROM    TD_CHAR_EQUIP_S4 WITH (NOLOCK)
                                WHERE    UID = @UID
                                --AND        ITEMID > 0
                                AND        USE_YN = 'Y'
                            ) B
                    ON        A.ITEM_ID = B.CHAR_ITEMID 
                    GROUP BY ITEM_ID
                ) Y
            ON    X.ITEM_ID = Y.ITEM_ID
                
    END 
 
            
END

USP_TUTORIAL_GET_STATUS

USE [Pangya_S4_TH]
GO
/******  StoredProcedure [dbo].[USP_TUTORIAL_GET_STATUS]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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

USP_CARD_GET_LIST

USE [Pangya_S4_TH]
GO
/****** Objet :  StoredProcedure [dbo].[USP_CARD_GET_LIST]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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

USP_CHAR_EQUIP_UCC_S4

USE [Pangya_S4_TH]
GO
/****** Object:  StoredProcedure [dbo].[USP_CHAR_EQUIP_UCC_S4]    Script Date: 12/04/2010 20:10:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    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[/QUOTE]

USP_CHAR_EQUIP_SAVE_S4
[QUOTE]USE [Pangya_S4_TH]
GO
/****** Objet :  StoredProcedure [dbo].[USP_CHAR_EQUIP_SAVE_S4]    Date de génération du script : 12/04/2010 00:48:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

USP_NICKNAME_UPDATE

USE [Pangya_S4_TH]
GO

/****** Object:  StoredProcedure [dbo].[USP_NICKNAME_UPDATE]    Script Date: 11/30/2010 19:44:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 CREATE PROCEDURE [dbo].[USP_NICKNAME_UPDATE]
@x int,
@y varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select 0
END
GO

Query que Cria todos os FileGroups e Users Necessarios e as DataBases

USE [master]
GO

/*******************************************
* Pangya Server 4.9 Database Bootstrapper *
* *
* Use at your own risk! *
* *
* Please edit the @DBUSER_PASSWORD for *
* your server. Leaving the default simply *
* isn't cool. *
* *
* USAGE: *
* 1. Run this query *
* 2. Run INI3_BILLDB.sql *
* 3. Run INI3_PY_ACCOUNT.sql *
* 4. Run Pangya_S4_TH.sql *
* 5. ??? (Add/Alter procedures, etc.) *
*******************************************/

DECLARE @DBUSER_PASSWORD VARCHAR(256)
DECLARE @DBUSER_CREATE_SQL NVARCHAR(256)

SET @DBUSER_PASSWORD = N'Password'    / <<<<< Coloque sua senha de SA = SQL

/* =========== Create users =========== */
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'ini3web')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [ini3web] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User ini3web already exists, skipping creation...'

IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'ini3gamebr')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [ini3gamebr] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User ini3gamebr already exists, skipping creation...'

IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'ini3webstat')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [ini3webstat] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User ini3webstat already exists, skipping creation...'

IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'pangya')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [pangya] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User pangya already exists, skipping creation...'

IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'Pangya_TH')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [Pangya_TH] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User Pangya_TH already exists, skipping creation...'

IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'Biller')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [Biller] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User Biller already exists, skipping creation...'

IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'pangyaclub')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [pangyaclub] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User pangyaclub already exists, skipping creation...'

IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'payletter')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [payletter] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User payletter already exists, skipping creation...'

IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'yogurting')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [yogurting] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User yogurting already exists, skipping creation...'

/* For people using the modified database we need a user Monk */
IF NOT EXISTS (SELECT loginname FROM dbo.syslogins WHERE name = N'Monk')
BEGIN
SELECT @DBUSER_CREATE_SQL = 'CREATE LOGIN [Monk] WITH PASSWORD=''' + @DBUSER_PASSWORD + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
EXEC sp_executesql @DBUSER_CREATE_SQL
END
ELSE
PRINT 'User Monk already exists, skipping creation...'

/* =========== ini3_py_account =========== */
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'ini3_py_account')
BEGIN
ALTER DATABASE [ini3_py_account] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [ini3_py_account] SET ONLINE
DROP DATABASE [ini3_py_account]
END

/* -- Recreate Database -- */
CREATE DATABASE [ini3_py_account]
GO

/* =========== INI3Bill_DB =========== */
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'INI3Bill_DB')
BEGIN
ALTER DATABASE [INI3Bill_DB] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [INI3Bill_DB] SET ONLINE
DROP DATABASE [INI3Bill_DB]
END

/* -- Recreate Database -- */
CREATE DATABASE [INI3Bill_DB]
GO

/* =========== Pangya_S4_TH =========== */
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Pangya_S4_TH')
BEGIN
/* -- Close all connections and drop the database -- */
ALTER DATABASE [Pangya_S4_TH] SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Pangya_S4_TH] SET ONLINE
DROP DATABASE [Pangya_S4_TH]
END

/* -- Recreate Database -- */
CREATE DATABASE [Pangya_S4_TH]

DECLARE @DATABASE_PATH VARCHAR(256)
SELECT @DATABASE_PATH = physical_name
FROM sys.master_files
WHERE name = N'Pangya_S4_TH'
SET @DATABASE_PATH = REPLACE(@DATABASE_PATH, 'Pangya_S4_TH.mdf', '')

PRINT 'Pangya_S4_TH database resides at: ' + @DATABASE_PATH

/* --- Add File Groups and Files to the database Pangya_S4_TH --- */
DECLARE @ADDFILE_CARD_SQL varchar(256)
DECLARE @ADDFILE_GLOG_SQL varchar(256)
DECLARE @ADDFILE_ITEM_SQL varchar(256)
DECLARE @ADDFILE_MAIL_SQL varchar(256)

SET @ADDFILE_CARD_SQL = 'ALTER DATABASE [Pangya_S4_TH] ADD File ( NAME = Pangya_S4_TH_Card, FILENAME = N''' + @DATABASE_PATH + '\Pangya_S4_TH_Card.mdf'', SIZE = 1MB ) TO FILEGROUP Pangya_S4_TH_Card'
SET @ADDFILE_GLOG_SQL = 'ALTER DATABASE [Pangya_S4_TH] ADD File ( NAME = Pangya_S4_TH_gLog, FILENAME = N''' + @DATABASE_PATH + '\Pangya_S4_TH_gLog.mdf'', SIZE = 1MB ) TO FILEGROUP Pangya_S4_TH_gLog'
SET @ADDFILE_ITEM_SQL = 'ALTER DATABASE [Pangya_S4_TH] ADD File ( NAME = Pangya_S4_TH_Item, FILENAME = N''' + @DATABASE_PATH + '\Pangya_S4_TH_Item.mdf'', SIZE = 1MB ) TO FILEGROUP Pangya_S4_TH_Item'
SET @ADDFILE_MAIL_SQL = 'ALTER DATABASE [Pangya_S4_TH] ADD File ( NAME = Pangya_S4_TH_Mail, FILENAME = N''' + @DATABASE_PATH + '\Pangya_S4_TH_Mail.mdf'', SIZE = 1MB ) TO FILEGROUP Pangya_S4_TH_Mail'

ALTER DATABASE [Pangya_S4_TH] ADD FILEGROUP Pangya_S4_TH_Card;
ALTER DATABASE [Pangya_S4_TH] ADD FILEGROUP Pangya_S4_TH_gLog;
ALTER DATABASE [Pangya_S4_TH] ADD FILEGROUP Pangya_S4_TH_Item;
ALTER DATABASE [Pangya_S4_TH] ADD FILEGROUP Pangya_S4_TH_Mail;

EXEC(@ADDFILE_CARD_SQL)
EXEC(@ADDFILE_GLOG_SQL)
EXEC(@ADDFILE_ITEM_SQL)
EXEC(@ADDFILE_MAIL_SQL)

GO

@Update_15.01.11


@Update_01.02.11


USP_CHAR_EQUIP_SAVE_S4 <<< Procedure que corrige o erro de desequipar a Roupa dos Char's

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

Vamos aos creditos: Advance/Darkknight/bubbastic/Tsukasa/Fernando.
-------------------------------------------------------------------------------------------------------------------