138 lines
3.1 KiB
Transact-SQL
138 lines
3.1 KiB
Transact-SQL
CREATE PROCEDURE [dbo].[up_char_sanction]
|
|
@kind tinyint, -- 1 : sanction, 2:release
|
|
@sanction_uid uniqueidentifier,
|
|
@personnel nvarchar(50),
|
|
@releaser nvarchar(50),
|
|
@comment nvarchar(128),
|
|
@character_id uniqueidentifier,
|
|
@sanction_type tinyint,
|
|
@sanction_count tinyint,
|
|
@sanction_period_type tinyint
|
|
as
|
|
set nocount on
|
|
|
|
begin tran
|
|
|
|
declare @get_uid uniqueidentifier,
|
|
@period int,
|
|
@cur_time datetime,
|
|
@end_time datetime,
|
|
@new_uid uniqueidentifier,
|
|
@is_insert tinyint
|
|
|
|
set @get_uid = '00000000-0000-0000-0000-000000000000'
|
|
|
|
if @sanction_period_type = 1 set @period = 3
|
|
if @sanction_period_type = 2 set @period = 7
|
|
if @sanction_period_type = 3 set @period = 10
|
|
if @sanction_period_type = 4 set @period = 15
|
|
if @sanction_period_type = 5 set @period = -1
|
|
|
|
-- select @get_uid=uid from Character_Sanction where
|
|
-- character_id = @character_id and sanction_count=@sanction_count
|
|
|
|
set @cur_time = getdate()
|
|
if @sanction_type <> 5
|
|
begin
|
|
set @end_time = dateadd(day, @period, @cur_time)
|
|
end
|
|
else
|
|
begin
|
|
set @end_time = NULL
|
|
end
|
|
|
|
set @new_uid = '00000000-0000-0000-0000-000000000000'
|
|
set @is_insert = 0
|
|
|
|
if @kind = 1
|
|
begin
|
|
if @get_uid <> '00000000-0000-0000-0000-000000000000'
|
|
begin
|
|
set @new_uid = @get_uid
|
|
set @is_insert = 1
|
|
update Character_Sanction set
|
|
sanction_type = @sanction_type,
|
|
sanction_count = @sanction_count,
|
|
sanction_period_type = @sanction_period_type,
|
|
period = @period,
|
|
start_time = @cur_time,
|
|
end_time = @end_time,
|
|
personnel = @personnel,
|
|
releaser = '', -- Rlease init
|
|
comment = '',
|
|
is_apply = 1,
|
|
reg_date = @cur_time
|
|
where uid = @get_uid
|
|
end
|
|
else
|
|
begin
|
|
set @new_uid = NEWID()
|
|
set @is_insert = 1
|
|
insert into Character_Sanction(
|
|
uid,
|
|
sanction_type,
|
|
sanction_count,
|
|
sanction_period_type,
|
|
period,
|
|
character_id,
|
|
start_time,
|
|
end_time,
|
|
personnel,
|
|
releaser,
|
|
comment,
|
|
is_apply,
|
|
reg_date)
|
|
values(
|
|
@new_uid,
|
|
@sanction_type,
|
|
@sanction_count,
|
|
@sanction_period_type,
|
|
@period,
|
|
@character_id,
|
|
@cur_time,
|
|
@end_time,
|
|
@personnel,
|
|
'', -- Rlease init
|
|
'',
|
|
1,
|
|
@cur_time)
|
|
end
|
|
end
|
|
else
|
|
begin
|
|
set @new_uid = @sanction_uid
|
|
set @is_insert = 0
|
|
|
|
select @new_uid = uid from Character_Sanction with (READUNCOMMITTED) where
|
|
character_id = @character_id and is_apply = 1
|
|
|
|
update Character_Sanction set
|
|
releaser = @releaser,
|
|
comment = @comment,
|
|
is_apply = 0
|
|
where character_id = @character_id
|
|
|
|
-- where uid = @sanction_uid
|
|
end
|
|
|
|
if EXISTS (select uid from Character_Sanction with (READUNCOMMITTED) where
|
|
character_id = @character_id and is_apply = 1)
|
|
begin
|
|
update CharacterTable set Block_YN = 'Y' where character_id=@character_id
|
|
end
|
|
else
|
|
begin
|
|
update CharacterTable set Block_YN = 'N' where character_id=@character_id
|
|
end
|
|
|
|
declare @ERR int
|
|
|
|
SET @ERR = @@ERROR
|
|
IF @ERR <> 0 BEGIN
|
|
RAISERROR('처리오류', 16, 10)
|
|
IF @@TRANCOUNT <> 0 ROLLBACK
|
|
RETURN (@ERR)
|
|
END
|
|
COMMIT TRAN
|
|
|
|
select NEW_ID = @new_uid, IS_INSERT = @is_insert
|