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
여기 아래에 질문자님이 원하시는 해법이 있네요. (try ~ catch)
https://technet.microsoft.com/ko-kr/library/ms179296(v=sql.105).aspx