MSSQL 동적프로시저 시 에러처리 부분을 OUTPUT 하고 싶습니다. 0 1 150

by 경상도곰남 [SQL Query] 동적프로시저 [2018.03.12 17:30:35]


MSSQL 동적프로시저 시 실행 후 에러여부를 체크 후 OUTPUT 하고 싶습니다.
고수님들의 답벼 부탁드립니다.

CREATE PROCEDURE [usp_DATAUpdate]
	@jt_idx				int,
	@jt_summary			text,
	@jt_necessity		text,
	@jt_creativity		text,
	@jt_objective		text,
	@jt_indicator		text,
	@jt_preparation		text,
	@jt_content			text,
	@jt_strategic		text,
	@jt_utilization		text,
	@jt_expected_effect	text,
	@mb_reg_id		varchar(20),  
	@intResult  	int output	
AS	
    Declare @strSQL nvarchar(max)	
		Begin
			Set @strSQL = 'UPDATE tbl_joint_technology Set '
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_summary) <> ''    And @jt_summary is not null Set @strSQL = @strSQL + ' jt_summary = '''+ CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_summary) + ''','												
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_necessity) <> ''   And @jt_necessity is not null Set @strSQL = @strSQL + ' jt_necessity = '''+ CONVERT(NVARCHAR(100), @jt_necessity) + CONVERT(VARCHAR(8000), @jt_necessity) +''','			
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_creativity) <> ''  And @jt_creativity is not null Set @strSQL = @strSQL + ' jt_creativity = '''+ CONVERT(NVARCHAR(100), @jt_creativity) + CONVERT(VARCHAR(8000), @jt_creativity) +''','
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_objective) <> ''   And @jt_objective is not null Set @strSQL = @strSQL + ' jt_objective = '''+ CONVERT(NVARCHAR(100), @jt_objective) + CONVERT(VARCHAR(8000), @jt_objective) +''','
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_indicator) <> ''   And @jt_indicator is not null Set @strSQL = @strSQL + ' jt_indicator = '''+ CONVERT(NVARCHAR(100), @jt_indicator) + CONVERT(VARCHAR(8000), @jt_indicator) +''','
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_preparation) <> '' And @jt_preparation is not null Set @strSQL = @strSQL + ' jt_preparation = '''+ CONVERT(NVARCHAR(100), @jt_preparation) + CONVERT(VARCHAR(8000), @jt_preparation) +''','
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_content) <> ''     And @jt_content is not null Set @strSQL = @strSQL + ' jt_content = '''+ CONVERT(NVARCHAR(100), @jt_content) + CONVERT(VARCHAR(8000), @jt_content) +''','
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_strategic) <> ''   And @jt_strategic is not null Set @strSQL = @strSQL + ' jt_strategic = '''+ CONVERT(NVARCHAR(100), @jt_strategic) + CONVERT(VARCHAR(8000), @jt_strategic) +''','
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_utilization) <> '' And @jt_utilization is not null Set @strSQL = @strSQL + ' jt_utilization = '''+ CONVERT(NVARCHAR(100), @jt_utilization) + CONVERT(VARCHAR(8000), @jt_utilization) +''','		
			IF CONVERT(NVARCHAR(100), @jt_summary) + CONVERT(VARCHAR(8000), @jt_expected_effect) <> '' And @jt_expected_effect is not null Set @strSQL = @strSQL + ' jt_expected_effect = '''+ CONVERT(NVARCHAR(100), @jt_expected_effect) + CONVERT(VARCHAR(8000), @jt_expected_effect) +''','
		 	Set @strSQL = @strSQL +' jt_modate ='''+ CONVERT(VARCHAR, getdate()) + ''','
			Set @strSQL = @strSQL +' jt_mouid  ='''+  @mb_reg_id + ''''								
			Set @strSQL = @strSQL + ' Where jt_idx = '+ CONVERT(VARCHAR, @jt_idx) 	 
		End						

		Exec sp_executesql @strSQL
		
		print @strSQL

 

by 신이만든지기 [2018.03.13 10:16:47]

여기 아래에 질문자님이 원하시는 해법이 있네요. (try ~ catch)

https://technet.microsoft.com/ko-kr/library/ms179296(v=sql.105).aspx

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입