CREATE PROCEDURE [dbo].[up_insert_guild_skill]
	(
		@guild_id           [uniqueidentifier],
		@learn_user_id      [uniqueidentifier],
		@skill_table_id		int,
		@skill_id			int,
		@skill_level		tinyint,
		@learn_cost			int,
		@is_update			tinyint		-- 0 이면 INSERT, 1 이면 UPDATE
	)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

	DECLARE @check_user_id		UNIQUEIDENTIFIER;
	DECLARE @check_user_gold	INT;
	DECLARE @old_skill_id		INT;
	DECLARE @guild_gold			INT;
	DECLARE @result_code		INT;
	
	SET @check_user_gold	= 0;
	SET @check_user_id		= NULL;			
	SET @check_user_gold	= 0;
	SET @old_skill_id		= NULL;
	SET @guild_gold			= 0;
	SET @result_code		= 1;
	
	BEGIN TRY
	
		BEGIN TRAN;
		
		SELECT TOP 1 @check_user_id=[character_id], @check_user_gold=[gold] FROM CharacterTable WITH (READUNCOMMITTED) WHERE [character_id] = @learn_user_id;
		SELECT TOP 1 @old_skill_id = [skill_id] FROM dbo.tbl_Guild_Skill WITH (READUNCOMMITTED) WHERE [guild_id]=@guild_id and [skill_id]=@skill_id;
		IF( (@check_user_id IS NOT NULL) AND (@check_user_gold >= @learn_cost) )
			BEGIN
				-- UPDATE 요청일 경우
				IF 1 = @is_update
					BEGIN

						IF @old_skill_id IS NULL
							BEGIN
							
								-- 길드 스킬이 이미 존재하지 않다면 에러 전달
								SET @result_code = -3;
														
							END
						ELSE
							BEGIN
							
								UPDATE dbo.tbl_Guild_Skill SET
									[skill_table_id]	= @skill_table_id,
									[skill_level]		= @skill_level,
									[skill_learn_time]	= GETDATE()
									WHERE [guild_id]=@guild_id AND [skill_id]=@skill_id
									
							END
						
					END
				-- INSERT 요청 처리	
				ELSE
					BEGIN
						
						IF @old_skill_id IS NULL
							BEGIN
							
								INSERT INTO tbl_Guild_Skill ( [guild_id], [skill_table_id], [skill_id], [skill_level], [skill_learn_time] )
									VALUES ( @guild_id, @skill_table_id, @skill_id, @skill_level, GETDATE() )
							
							END
						ELSE
							BEGIN
							
								-- 길드 스킬이 존재 한다면 에러 전달
								SET @result_code = -2;
								
							END
						
					END
				
				-- 비용 차감	
				IF 1 = @result_code
					BEGIN
						DECLARE @result_gold INT;
						SET @result_gold = (@check_user_gold - @learn_cost);
						IF( @result_gold < 0 )
							BEGIN
								SET @result_gold = 0;
							END
						
						UPDATE [dbo].[CharacterTable] SET [gold] = @result_gold WHERE [character_id] = @learn_user_id
					END
			END
		ELSE
			BEGIN
			
				-- 프로시저 조건 검사 실패
				SET @result_code = -5;
				
			END
		
		COMMIT TRAN;
		
		SELECT result_code = @result_code;	--결과 코드 전달
			
	END TRY
	BEGIN CATCH
		
		ROLLBACK TRAN;
		
		SELECT result_code = -1;
	
	END CATCH	
END