Just an idea - Page 2

Grr i added aloken in sql db and still nothing
in SP_CHAR_CREATE

Maybe im wrong

/************************************************** ****************
* 작성자 : 한지욱
* 작성일 : 2004.10.11(월)
* 내 용 : 캐릭터 생성
* 반환값 : -1 : character_no 체번 실패
-2 : user_character 등록실패
-3 : user_suit 등록실패
-4 : user_bag 등록오류
-5 : user_skill 등록오류
-6 : user_slot 등록오류
-7 : user_quest 등록오류
-11 : 중복 캐릭터명 입력
-12 : 금지 캐릭터명 입력
0:성공
************************************************** ****************
* 수정자 : 한지욱
* 수정일 : 2004.10.20(수)
* 수정내역 : 캐릭터 생성시 bag정보 입력
************************************************** ****************
* 수정자 : 한지욱
* 수정일 : 2004.10.21(목)
* 수정내역 : character_no OUTPUT parameter 추가
************************************************** ****************
* 수정자 : 한지욱
* 수정일 : 2004.10.25(월)
* 수정내역 : 캐릭터명 중복체크 / 필터링 추가
************************************************** ****************
* 수정자 : 한지욱
* 수정일 : 2005.03.19(월)
* 수정내역 : Character_Info 데이터타입 변경
************************************************** ****************
* 수정자 : 한지욱
* 수정일 : 2005.03.24(목)
* 수정내역 : 1) 캐릭터생성시 기본정보 DB에서 제공
2) 기본정보 : Char_Info, Char_Suit, Char_Skill, Char_Slot, Char_Quest
************************************************** ****************
* 수정자 : 한지욱
* 수정일 : 2005.03.25(금)
* 수정내역 : 캐릭터정보에 Storage 추가
************************************************** ****************
* 수정자 : Hanjiwook
* 수정일 : 2005.08.04(THU)
* 수정내역 : 캐릭터정보에 PK정보 추가 (wPKCount, wChaoticLevel, nShield)
************************************************** ****************
* 수정자 : Hanjiwook
* 수정일 : 2005.09.05(MON)
* 수정내역 : 스탯클리어카운트 추가 (byStatClearCount)
************************************************** ****************
* 수정자 : Hanjiwook
* 수정일 : 2005.09.06(TUE)
* 수정내역 : PC 기타정보 추가 (dwFlag)
************************************************** ****************
* 수정자 : Hanjiwook
* 수정일 : 2005.10.20(THU)
* 수정내역 : 아이템시리얼넘버 Type 변경(bigint -> binary(16)
************************************************** ****************
* 수정자 : Hanjiwook
* 수정일 : 2005.11.07(MON)
* 수정내역 : 아이템테이블 등록일, 만료일 추가 (by 기간제아이템)
************************************************** ****************
* 수정자 : Hanjiwook
* 수정일 : 2005.11.30(WED)
* 수정내역 : 아이템남은시간 분 -> 초
************************************************** ****************
* 수정자 : Choijihwan
* 수정일 : 2006.02.11(SAT)
* 수정내역 : user_character테이블 PVP Columns 추가로
@char_info OUTPUT 에 다음정보 추가.
DWORD dwPVPPoint; // PVP 점수
WORD wWinRecord; // 승리 횟수
WORD wLoseRecord; // 패배 횟수
WORD wDrawRecord; // 비긴 횟수
************************************************** ****************
* 수정자 : Hanjiwook
* 수정일 : 2006.04.05(WED)
* 수정내역 :
1. 캐릭터생성시 아이템시리얼 하드코딩 (0x00000000000000000000000000000000)
2. dbo.FN_GetRemainSecond 함수 Call 오류 수정
************************************************** ****************
* 수정자 : Choijihwan
* 수정일 : 2006.12.26(WEB)
* 수정내역 : Add "dwSupplyPoint" column
DWORD dwSupplyPoint;
************************************************** ****************/
CREATE PROCEDURE dbo.SP_CHAR_CREATE
@user_no varchar(14) ,
@character_name varchar(40) ,
@v_byPCClass INT ,
@v_mapIndex INT ,
@user_ip_addr varbinary(4) ,
@character_no varchar(18) OUTPUT ,
@char_info varbinary(256) OUTPUT ,
@char_suit varbinary(1000) OUTPUT ,
@char_suit_serial varbinary(1000) OUTPUT ,
@char_suit_limit varbinary(1000) OUTPUT ,
@sp_rtn INT OUTPUT
AS

DECLARE @v_char_info varbinary(256)
DECLARE @ipt_date char(8)
DECLARE @ipt_time datetime
DECLARE @rowcnt int
DECLARE @v_ret int
DECLARE @char_gubun char(1)
DECLARE @regdate varchar(8)
DECLARE @seq_no numeric
DECLARE @tab_nm varchar(20)
DECLARE @m_date varchar(6)
DECLARE @spid varchar(3)
DECLARE @rand_no varchar(1)
DECLARE @wh_char_no VARCHAR(14)

DECLARE @wLength INT
DECLARE @dwAdv BIGINT
DECLARE @dwPeerage BIGINT
DECLARE @dwExp BIGINT
DECLARE @dwMoney BIGINT
DECLARE @dwStoreMoney BIGINT
DECLARE @dwStorageMoney BIGINT
DECLARE @nHP INT
DECLARE @nMP INT
DECLARE @wStr INT
DECLARE @wDex INT
DECLARE @wCon INT
DECLARE @wSpr INT
DECLARE @wPosX INT
DECLARE @wPosY INT
DECLARE @wRetPosX INT
DECLARE @wRetPosY INT
DECLARE @wMapIndex INT
DECLARE @wRetMapIndex INT
DECLARE @wStatPoint INT
DECLARE @wSkillPoint INT
DECLARE @wLevel INT
DECLARE @byPCClass INT
DECLARE @byDirection INT
DECLARE @byRetDirection INT
DECLARE @bySkillClearCount INT
DECLARE @byStatClearCount INT -- 스탯클리어카운트
DECLARE @wPKCount INT -- PK수
DECLARE @wChaoticLevel INT -- 카오수치
DECLARE @nShield INT -- 실드포인트(SP)
DECLARE @dwFlag INT -- PC INFO 정보 Flag

DECLARE @v_cur_no INT
DECLARE @v_suit_len INT
DECLARE @v_character_suit VARBINARY(1000)
DECLARE @v_char_suit_serial VARBINARY(1000)
DECLARE @v_char_suit_limit VARBINARY(1000)
DECLARE @v_position INT
DECLARE @v_field VARBINARY(29)
DECLARE @v_dwSerialInfo binary(19)
DECLARE @v_dwlimitInfo binary(15)

BEGIN

-------------------------------------------------------------------
-- 캐릭터번호 생성규칙
-- 형식 : char(14) - 캐릭터구분 + YYMM + 일련번호(9) -> A0410123456789
-- 캐릭터구분 : 기사(0)-'A', 궁사(1)-'B', 법사(2)-'C', 소환사(3)-'D', 힐러(4)-'E', 워리어(5)-'F'
-------------------------------------------------------------------

SET @character_no = '000000000000000000'
SET @char_info = 0x1234567890
SET @char_suit = 0X0002
SET @char_suit_serial = 0x0002
SET @char_suit_limit = 0x0002

SET @sp_rtn = 0
SET @v_ret = 0
SET @ipt_time = GetDate()
SET @spid = CAST(dbo.FN_GetAutoNo(@@spid,3) as varchar(3))

-- 캐릭터명 체크 (중복체크 / 필터링체크)
EXEC sp_char_name_check @character_name, @v_ret OUTPUT

IF @v_ret < 0 -- 캐릭터명 오류
BEGIN
SET @sp_rtn = @v_ret
RETURN
END

-- 캐릭터구분 Parsing
SELECT @char_gubun = CASE @v_byPCClass
WHEN 0 THEN 'A'
WHEN 1 THEN 'B'
WHEN 2 THEN 'C'
WHEN 3 THEN 'D'
WHEN 4 THEN 'E'
WHEN 5 THEN 'F'
WHEN 6 THEN 'G'
ELSE 'Z'
END

-- 관리코드 테이블네임 설정 (테이블명_캐릭터구분)
SET @tab_nm = 'USER_CHARACTER'+'_'+@char_gubun
SET @regdate = SUBSTRING(CONVERT(varchar(10),@ipt_time,112),1,6)+ '01'

-- 캐릭터 일련번호 체번
EXEC sp_com_seqno @tab_nm, @regdate, @seq_no OUTPUT

IF @seq_no < 1 -- 일련번호 체번오류
BEGIN
SET @sp_rtn = @seq_no
RETURN
END

-- character_no 생성
SET @m_date = SUBSTRING(CONVERT(varchar(10),@ipt_time,112),3,4)
SET @character_no = @char_gubun + @m_date + @spid + dbo.FN_GetAutoNo(@seq_no,10)

-- 기본캐릭터 생성위한 검색조건 생성
SET @rand_no = CAST(3*Rand()+1 as INT)
SET @wh_char_no = 'DEKARON'+CAST(@v_byPCClass as varchar(1))+'000001'

SELECT @dwAdv = dwAdv ,
@dwPeerage = dwPeerage ,
@dwExp = dwExp ,
@dwMoney = dwMoney ,
@dwStoreMoney = dwStoreMoney ,
@dwStorageMoney = dwStorageMoney ,
@nHP = nHP ,
@nMP = nMP ,
@wStr = wStr ,
@wDex = wDex ,
@wCon = wCon ,
@wSpr = wSpr ,
@wStatPoint = wStatPoint ,
@wSkillPoint = wSkillPoint ,
@wLevel = wLevel ,
@byPCClass = byPCClass ,
@bySkillClearCount = bySkillClearCount ,
@byStatClearCount = byStatClearCount ,
@wPKCount = wPKCount ,
@wChaoticLevel = wChaoticLevel ,
@nShield = nShield ,
@dwFlag = dwFlag
FROM dbo.user_character WITH(NOLOCK)
WHERE character_no = @wh_char_no

SELECT @wPosX = wPosX ,
@wPosY = wPosY ,
@wRetPosX = wRetPosX ,
@wRetPosY = wRetPosY ,
@wMapIndex = wMapIndex ,
@wRetMapIndex = wRetMapIndex ,
@byDirection = byDirection ,
@byRetDirection = byRetDirection
FROM dbo.char_mappos WITH(NOLOCK)
WHERE mapIndex = @v_mapIndex
AND line_no = @rand_no

-- 등록일자
SET @ipt_date = convert(char(8),getdate(),112)

BEGIN TRANSACTION
-------------------------------------------------------------------
-- 1. insert into user_character(캐릭터기본정보) table
-------------------------------------------------------------------
INSERT INTO user_character
(
character_no ,
character_name ,
user_no ,
dwAdv ,
dwPeerage ,
dwExp ,
dwMoney ,
dwStoreMoney ,
dwStorageMoney ,
nHP ,
nMP ,
wStr ,
wDex ,
wCon ,
wSpr ,
wPosX ,
wPosY ,
wRetPosX ,
wRetPosY ,
wMapIndex ,
wRetMapIndex ,
wStatPoint ,
wSkillPoint ,
wLevel ,
byPCClass ,
byDirection ,
byRetDirection ,
bySkillClearCount ,
byStatClearCount ,
wPKCount ,
wChaoticLevel ,
nShield ,
dwFlag ,
login_flag ,
ipt_date ,
ipt_time ,
user_ip_addr
)
VALUES
(
@character_no ,
@character_name ,
@user_no ,
@dwAdv ,
@dwPeerage ,
@dwExp ,
@dwMoney ,
@dwStoreMoney ,
@dwStorageMoney ,
@nHP ,
@nMP ,
@wStr ,
@wDex ,
@wCon ,
@wSpr ,
@wPosX ,
@wPosY ,
@wRetPosX ,
@wRetPosY ,
@wMapIndex ,
@wRetMapIndex ,
@wStatPoint ,
@wSkillPoint ,
@wLevel ,
@byPCClass ,
@byDirection ,
@byRetDirection ,
@bySkillClearCount ,
@byStatClearCount ,
@wPKCount ,
@wChaoticLevel ,
@nShield ,
@dwFlag ,
'Y' ,
@ipt_date ,
@ipt_time ,
@user_ip_addr
)

IF @@ERROR <> 0 OR @@ROWCOUNT <> 1
-- user_character 등록실패
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -2
RETURN
END

-------------------------------------------------------------------
-- 2. insert into suit(캐릭터착용정보) table
-------------------------------------------------------------------
INSERT INTO USER_SUIT
(
character_no ,
line_no ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
)
SELECT @character_no ,
line_no ,
byHeader ,
wIndex ,
0x00000000000000000000000000000000,
info ,
getDate() ,
reg_bindate ,
exp_bindate
FROM dbo.user_suit WITH(NOLOCK)
WHERE character_no = @wh_char_no

IF @@ERROR <> 0
-- user_suit 등록실패
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -3
RETURN
END

-------------------------------------------------------------------
-- 3. insert into bag(캐릭터인벤토리정보) table
-------------------------------------------------------------------
INSERT INTO USER_BAG
(
character_no ,
line_no ,
byHeader ,
wIndex ,
dwSerialNumber ,
info ,
upt_time ,
reg_bindate ,
exp_bindate
)
SELECT @character_no ,
line_no ,
byHeader ,
wIndex ,
0x00000000000000000000000000000000,
info ,
getDate() ,
reg_bindate ,
exp_bindate
FROM dbo.user_bag WITH(NOLOCK)
WHERE character_no = @wh_char_no

IF @@ERROR <> 0
-- user_bag 등록실패
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -4
RETURN
END

-------------------------------------------------------------------
-- 4. insert into skill (캐릭터스킬정보) table
-------------------------------------------------------------------
INSERT INTO USER_SKILL
(
character_no ,
line_no ,
info ,
ipt_time ,
upt_time
)
SELECT @character_no ,
line_no ,
info ,
ipt_time ,
getDate()
FROM dbo.user_skill WITH(NOLOCK)
WHERE character_no = @wh_char_no

IF @@ERROR <> 0
-- user_skill 등록실패
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -5
RETURN
END

-------------------------------------------------------------------
-- 5. insert into slot (캐릭터슬롯정보) table
-------------------------------------------------------------------
INSERT INTO USER_SLOT
(
character_no ,
line_no ,
info ,
ipt_time ,
upt_time
)
SELECT @character_no ,
line_no ,
info ,
ipt_time ,
getDate()
FROM dbo.user_slot WITH(NOLOCK)
WHERE character_no = @wh_char_no

IF @@ERROR <> 0
-- user_slot 등록실패
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -6
RETURN
END

-------------------------------------------------------------------
-- 5. insert into Quest_Doing (캐릭터 퀘스트 정보) table
-------------------------------------------------------------------
INSERT INTO USER_QUEST_DOING
(
character_no ,
q_index ,
q_count_0 ,
q_count_1 ,
q_count_2 ,
q_count_3 ,
q_start_time ,
upt_time
)
SELECT @character_no ,
q_index ,
q_count_0 ,
q_count_1 ,
q_count_2 ,
q_count_3 ,
q_start_time ,
getDate()
FROM dbo.USER_QUEST_DOING WITH(NOLOCK)
WHERE character_no = @wh_char_no

IF @@ERROR <> 0
-- user_quest_doing 등록실패
BEGIN
ROLLBACK TRANSACTION
SET @sp_rtn = -7
RETURN
END

-- char_info binary Data OutPut
SELECT @v_char_info = CAST( 91 as varbinary(2)) +
CAST(dwAdv as varbinary(4)) +
CAST(dwPeerage as varbinary(4)) +
CAST(dwExp as varbinary(4)) +
CAST(dwMoney as varbinary(4)) +
CAST(dwStoreMoney as varbinary(4)) +
CAST(dwStorageMoney as varbinary(4)) +
CAST(nHP as varbinary(4)) +
CAST(nMP as varbinary(4)) +
CAST(wStr as varbinary(2)) +
CAST(wDex as varbinary(2)) +
CAST(wCon as varbinary(2)) +
CAST(wSpr as varbinary(2)) +
CAST(wPosX as varbinary(2)) +
CAST(wPosY as varbinary(2)) +
CAST(wRetPosX as varbinary(2)) +
CAST(wRetPosY as varbinary(2)) +
CAST(wMapIndex as varbinary(2)) +
CAST(wRetMapIndex as varbinary(2)) +
CAST(wStatPoint as varbinary(2)) +
CAST(wSkillPoint as varbinary(2)) +
CAST(wLevel as varbinary(2)) +
CAST(byPCClass as varbinary(1)) +
CAST(byDirection as varbinary(1)) +
CAST(byRetDirection as varbinary(1)) +
CAST(bySkillClearCount as varbinary(1)) +
CAST(byStatClearCount as varbinary(1)) +
CAST(wPKCount as varbinary(2)) +
CAST(wChaoticLevel as varbinary(2)) +
CAST(nShield as varbinary(4)) +
CAST(dwFlag as varbinary(4)) +
CAST(IsNull(dwPVPPoint , 0) as varbinary(4)) + --PVP : PVP 점수
CAST(IsNull(wWinRecord , 0) as varbinary(2)) + --PVP : 승리 횟수
CAST(IsNull(wLoseRecord , 0) as varbinary(2)) + --PVP : 패배 횟수
CAST(IsNull(wDrawRecord , 0) as varbinary(2)) + --PVP : 비긴 횟수
CAST(IsNull(dwSupplyPoint, 0) as varbinary(4)) --보급 물자 포인트
FROM dbo.user_character WITH(NOLOCK)
WHERE character_no = @character_no

SET @char_info = @v_char_info

-- char_suit OutPut Start
SET @v_suit_len = 2
SET @v_character_suit = NULL
SET @v_char_suit_serial = NULL
SET @v_char_suit_limit = NULL

DECLARE cur_suit CURSOR FAST_FORWARD
FOR
SELECT line_no,
CAST(byHeader as varbinary(2)) +
CAST(wIndex as varbinary(2)) +
info ,
CAST(line_no as varbinary(1)) +
CAST(wIndex as varbinary(2)) +
dwSerialNumber ,
CAST(line_no as varbinary(1)) +
CAST(wIndex as varbinary(2)) +
dbo.FN_GetRemainSecond(GetDate(), exp_bindate) +
ISNULL(reg_bindate, 0x00000000) +
ISNULL(exp_bindate, 0x00000000)
FROM dbo.user_suit WITH(NOLOCK)
WHERE character_no = @character_no

OPEN cur_suit

FETCH NEXT FROM cur_suit INTO @v_position, @v_field, @v_dwSerialInfo, @v_dwlimitInfo

WHILE @@FETCH_STATUS = 0
BEGIN
IF ((@v_suit_len + DATALENGTH(@v_field)) > 1000) OR (DATALENGTH(@v_char_suit_serial) + 2 + DATALENGTH(@v_dwSerialInfo) > 1000) OR (DATALENGTH(@v_char_suit_limit) + 2 + DATALENGTH(@v_dwlimitInfo) > 1000)
BEGIN
CLOSE cur_suit
DEALLOCATE cur_suit
ROLLBACK TRAN
SET @sp_rtn = -5
RETURN
END

IF @v_character_suit IS NULL
SET @v_character_suit = CONVERT(VARBINARY(1), DATALENGTH(@v_field) + 2)
ELSE
SET @v_character_suit = @v_character_suit + CONVERT(VARBINARY(1), DATALENGTH(@v_field) + 2)

SET @v_character_suit = @v_character_suit + CONVERT(VARBINARY(1), @v_position)
SET @v_character_suit = @v_character_suit + @v_field

--// Serial Packet
IF @v_char_suit_serial IS NULL
SET @v_char_suit_serial = @v_dwSerialInfo
ELSE
SET @v_char_suit_serial = @v_char_suit_serial + @v_dwSerialInfo
--// Limit Packet
IF @v_char_suit_limit IS NULL
SET @v_char_suit_limit = @v_dwlimitInfo
ELSE
SET @v_char_suit_limit = @v_char_suit_limit + @v_dwlimitInfo

SET @v_suit_len = @v_suit_len + 2 + DATALENGTH(@v_field)

FETCH NEXT FROM cur_suit INTO @v_position, @v_field, @v_dwSerialInfo, @v_dwlimitInfo
END

--// Item Info Check
IF @v_character_suit IS NULL
SET @v_character_suit = CONVERT( BINARY(2), @v_suit_len )
ELSE
SET @v_character_suit = CONVERT( BINARY(2), @v_suit_len ) + @v_character_suit
--// Item Serial Check
IF @v_char_suit_serial IS NULL
SET @v_char_suit_serial = 0x0002
ELSE
SET @v_char_suit_serial = CONVERT( BINARY(2), DATALENGTH(@v_char_suit_serial)+2 ) + @v_char_suit_serial
--// Item Limit Check
IF @v_char_suit_limit IS NULL
SET @v_char_suit_limit = 0x0002
ELSE
SET @v_char_suit_limit = CONVERT( BINARY(2), DATALENGTH(@v_char_suit_limit)+2 ) + @v_char_suit_limit

SET @char_suit = @v_character_suit
SET @char_suit_serial = @v_char_suit_serial
SET @char_suit_limit = @v_char_suit_limit

CLOSE cur_suit
DEALLOCATE cur_suit
-- char_suit OutPut End

COMMIT TRANSACTION
SET @sp_rtn = 0
RETURN
END

GO