item2row, klappt wunderbar. (CHR_DBF)
[COLOR="DeepSkyBlue"]item2row_str[/COLOR]
CREATE proc [dbo].[item2row_str]
as
set nocount on
truncate table item2row_tbl
declare @m_idPlayer char(7),@serverindex char(2),@m_Inventory varchar(6000),@m_Bank varchar(6000)
declare item2row_cursor cursor fast_forward for
select m_idPlayer,serverindex,m_Inventory,m_Bank
from view_character_level(nolock)
order by m_idPlayer
open item2row_cursor
fetch next from item2row_cursor
into @m_idPlayer,@serverindex,@m_Inventory,@m_Bank
while @@fetch_status = 0
begin
if @m_Inventory <> '$'
insert item2row_tbl
(m_idPlayer,serverindex,m_dwObjId,m_dwItemId,m_nItemNum,m_nAbilityOption, ItemResist, ResistAbilityOpt, m_position)
select m_idPlayer,serverindex,m_dwObjId,m_dwItemId,m_nItemNum,isnull(m_nAbilityOption,0), ItemResist, ResistAbilityOpt, 'I'
-- from master.dbo.fn_item2row(@m_idPlayer,@serverindex,@m_Inventory)
from dbo.fn_item2row(@m_idPlayer,@serverindex,@m_Inventory)
if @m_Bank <> '$'
insert item2row_tbl
(m_idPlayer,serverindex,m_dwObjId,m_dwItemId,m_nItemNum,m_nAbilityOption, ItemResist, ResistAbilityOpt, m_position)
select m_idPlayer,serverindex,m_dwObjId,m_dwItemId,m_nItemNum,isnull(m_nAbilityOption,0), ItemResist, ResistAbilityOpt, 'B'
-- from master.dbo.fn_item2row(@m_idPlayer,@serverindex,@m_Bank)
from dbo.fn_item2row(@m_idPlayer,@serverindex,@m_Bank)
fetch next from item2row_cursor
into @m_idPlayer,@serverindex,@m_Inventory,@m_Bank
--print @m_idPlayer
end
close item2row_cursor
deallocate item2row_cursor
set nocount off
return
Alles anzeigen
[COLOR="DeepSkyBlue"]item2row (Function)[/COLOR]
CREATE function [dbo].[fn_item2row]
(@m_idPlayer char(7),@serverindex char(2),@arr_item varchar(6000))
returns @item_row table (m_idPlayer char(7),serverindex char(2),m_dwObjId int,m_dwItemId int, m_nItemNum int, m_nUniqueNo bigint, m_nAbilityOption int, ItemResist int, ResistAbilityOpt int)
as
begin
declare @item varchar(255),@item_property varchar(50)
declare @pos_s int,@pos_e int
declare @pos_sub_s int,@pos_sub_e int
declare @m_dwobjid varchar(11)
declare @m_dwitemid varchar(11)
declare @m_nitemnum varchar(11)
declare @m_nUniqueNo varchar(11)
declare @m_nabilityoption varchar(11)
--------------------------------------------------
/* 추가부분 Start */
--------------------------------------------------
declare @ItemResist varchar(11)
declare @ResistAbilityOpt varchar(11)
--------------------------------------------------
/* 추가부분 End */
--------------------------------------------------
declare @idx int
set @pos_e = 1
while 1=1
begin
set @pos_s = charindex('/',@arr_item,@pos_e)
if @pos_s = 0
break
set @item = substring(@arr_item,@pos_e,@pos_s-@pos_e)
set @pos_sub_e = 1
set @idx = 1
while @idx <= 15 -- <- 13에서 15로 수정
begin
set @pos_sub_s = charindex(',',@item,@pos_sub_e)
if @pos_sub_s = 0
break
set @item_property = substring(@item,@pos_sub_e,@pos_sub_s-@pos_sub_e)
if @idx = 1
set @m_dwobjid = @item_property
if @idx = 2
set @m_dwitemid = @item_property
if @idx = 6
set @m_nitemnum = @item_property
if @idx=12
set @m_nUniqueNo=@item_property
if @idx = 13
set @m_nabilityoption = @item_property
--------------------------------------------------
/* 추가부분 Start */
--------------------------------------------------
if @idx = 14
set @ItemResist = @item_property
if @idx = 15
set @ResistAbilityOpt = @item_property
--------------------------------------------------
/* 추가부분 End */
--------------------------------------------------
set @idx = @idx + 1
set @pos_sub_e = @pos_sub_s + 1
end
if not exists(select * from @item_row where m_dwObjId = @m_dwobjid) and @m_dwobjid is not null and @m_dwitemid is not null
insert @item_row
values
(@m_idPlayer,@serverindex,@m_dwobjid,@m_dwitemid,@m_nitemnum,@m_nUniqueNo, @m_nabilityoption, @ItemResist, @ResistAbilityOpt)
set @pos_e = @pos_s + 1
end
return
end
Alles anzeigen
[COLOR="DeepSkyBlue"]item2row (Table)[/COLOR]
CREATE TABLE [dbo].[item2row_tbl](
[m_idPlayer] [char](7) NULL,
[serverindex] [char](2) NULL,
[m_dwObjId] [int] NULL,
[m_nItemNum] [int] NULL,
[m_nAbilityOption] [int] NULL,
[m_dwItemId] [varchar](32) NULL,
[m_position] [char](1) NULL,
[ItemResist] [int] NULL,
[ResistAbilityOpt] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[item2row_tbl] ADD CONSTRAINT [DF__item2row___ItemR__153B1FDF] DEFAULT ((0)) FOR [ItemResist]
GO
ALTER TABLE [dbo].[item2row_tbl] ADD CONSTRAINT [DF__item2row___Resis__162F4418] DEFAULT ((0)) FOR [ResistAbilityOpt]
GO
Alles anzeigen
Ich sag nur so viel:
Die Prozedur erstellt rows für jeden Slot von jedem Char.
Sollte als Tipp genügen oO
