[sql]usp_card_spcl_pop_s5

Spoiler:

Code:

---

USE [Pangya_S4_TH]
GO
/****** Object:  StoredProcedure [dbo].[USP_CARD_SPCL_POP_S5]    Script Date: 04/17/2011 22:00:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[USP_CARD_SPCL_POP_S5] ( 
      @UID                INT 
    , @CARD_TYPEID        INT 
    , @CARD_ITEMID        INT
    , @UNK                INT
    , @UNK2            INT 
    , @CARD_TYPE        TINYINT  -- ADD
    , @Duration        INT
) -- -- WITH ENCRYPTION

AS 
/********************************************* 
S5 V1.0 SPECIAL 카드 사용
 
리턴코드 추가 및 카드타입 체크

ERRCODE
0 : 성공
1 : SYS ERR
4 : CARD_TYPE 스페셜 카드아님
9 : SPCL 카드 수량 없음

***********************************************/ 
BEGIN 
SET NOCOUNT ON 

    DECLARE @CARD_POP_ERR INT, @CARD_PUSH_ERR INT, @OUT_CARD_ITEMID INT,
            @SPCL_ITEM_ID INT, @TYPES TINYINT, @QTY INT, @ERR INT,
            @SET_DT DATETIME

    SET @QTY = 1
    SET @SPCL_ITEM_ID = 0
    SET @SET_DT = ''
    SET @ERR = 0
    SET @TYPES = 2 -- 스페셜카드 사용시


 
    IF ( @CARD_TYPE = 2 ) BEGIN 

        -- 카드소모
        EXEC USP_CARD_POP_S4 @UID, @CARD_ITEMID, @CARD_TYPEID, @QTY, @TYPES, @CARD_POP_ERR OUTPUT

        IF @CARD_POP_ERR = 0 BEGIN
            BEGIN TRAN

            SELECT @SPCL_ITEM_ID = SPCL_IDX, @SET_DT = DATEADD(MI, @Duration, END_DT)  FROM DBO.TD_CARD_SPCL WITH (NOLOCK) 
            WHERE UID = @UID AND CARD_TYPEID = @CARD_TYPEID AND END_DT > GETDATE()


            IF ( @SPCL_ITEM_ID > 0 ) BEGIN
                 
                UPDATE DBO.TD_CARD_SPCL 
                SET END_DT = @SET_DT
                WHERE UID = @UID AND CARD_TYPEID = @CARD_TYPEID AND SPCL_IDX = @SPCL_ITEM_ID

                SELECT @ERR = CASE WHEN ( @@ROWCOUNT <> 1 AND @@ERROR != 0 ) THEN @ERR + 1 ELSE 0 END
                 
            END ELSE BEGIN     
               
                INSERT INTO DBO.TD_CARD_SPCL (UID, CARD_TYPEID, USE_DT, END_DT) 
                VALUES (@UID, @CARD_TYPEID, GETDATE(), DATEADD(MI, @Duration, GETDATE()) )

                SELECT    @ERR = CASE WHEN ( @@ROWCOUNT <> 1 AND @@ERROR != 0 ) THEN @ERR + 1 ELSE 0 END
                        , @SPCL_ITEM_ID = SCOPE_IDENTITY() -- : CARD_ITEMID 추가 고민

            END

            -- RETURN RESULT
            IF ( @ERR = 0 ) BEGIN
                COMMIT TRAN
                SELECT    ERRCODE = @ERR, END_DT = DATEDIFF(MI, GETDATE(), END_DT)
                FROM TD_CARD_SPCL WHERE SPCL_IDX = @SPCL_ITEM_ID     

            END ELSE BEGIN
                ROLLBACK TRAN
                SELECT    ERRCODE = @ERR, END_DT = NULL 
            END 

        END ELSE BEGIN
            -- ROLLBACK CARD : ADD
            -- EXEC USP_CARD_PUSH_S4    @UID, @CARD_TYPEID, @QTY, 'SPCL_ERR', @CARD_PUSH_ERR OUTPUT, @OUT_CARD_ITEMID OUTPUT
            SELECT    ERRCODE = @CARD_POP_ERR, END_DT = NULL 
        END     

    END ELSE BEGIN
        -- CHECK CARD_TYPE : ONLY SPECIAL CARD
        SET @ERR = 4
        SELECT    ERRCODE = @ERR, END_DT = NULL 

        -- FINAL NEW CARD LOG
--        INSERT INTO Pangya_GDBLog.DBO.TZ_CARD_LOG (TYPES, ERRCODE, UID, ORI_ITEMID, CARD_ITEMID, CARD_TYPEID, QTY, FINAL_QTY, REGDATE)
--        SELECT    @TYPES, ERRCODE = @ERR, @UID, ORI_ITEMID = @SPCL_ITEM_ID, CARD_ITEMID = @CARD_ITEMID, CARD_TYPEID = @CARD_TYPEID,
--                ADD_QTY = CASE WHEN (@ERR != 0) THEN 0 ELSE @QTY END,
--                QTY = CASE WHEN (@ERR != 0) THEN 0 ELSE @QTY END, GETDATE()
        -- FINAL NEW CARD LOG

    END

END

---

Yeah, I know. Had to be done, but no one did it. For those who don't have this procedure, change "ALTER" to "CREATE".

thx for sharing !

You, my friend, just made a lot of people very happy ;)

Nice release.

We need to help each other, this is why this community lives. Could you update your Cardpacks with the Special Cards then?

I did that a while ago with some help from another person.
But yeah, my card packs have special cards inside (exp boost, pang boost, course bonusses, feather effect, cadie effect....)

how i add new cards to my server?

If you just check the database for card-related tables, it should become clear without too much trouble.