[MSSQL] 예외,에러 처리

Posted on 2018. 11. 29. 13:31
Filed Under DB

▼TRY..CATCH

-- TRY..CATCH 사용예
BEGIN TRY  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
        -- 프로시저 파라미터 사용 가능
 	DECLARE @ParamInfoStr varchar(1000)
	= '@ResultDate='+CASE WHEN @ResultDate IS NULL THEN 'null' ELSE CONVERT(varchar(19), @ResultDate, 120) END
		+', @ModelID='++CASE WHEN @ModelID IS NULL THEN 'null' ELSE CAST(@ModelID as varchar) END

	-- error retrieval .  
	SELECT  
		 ERROR_NUMBER() AS ErrorNumber  
		,ERROR_SEVERITY() AS ErrorSeverity  
		,ERROR_STATE() AS ErrorState  
		,ERROR_PROCEDURE() AS ErrorProcedure  
		,ERROR_LINE() AS ErrorLine  
		,ERROR_MESSAGE() AS ErrorMessage; 

	--출력
	PRINT
	   'ERROR_NUMBER = ' + CAST(ERROR_NUMBER() AS VARCHAR)
	 +'ERROR_SEVERITY = ' + CAST(ERROR_SEVERITY() AS VARCHAR)
	 +'ERROR_STATE = ' + CAST(ERROR_STATE() AS VARCHAR)
	 +'ERROR_PROCEDURE = ' + ERROR_PROCEDURE()
	 +'ERROR_LINE = ' + CAST(ERROR_LINE() AS VARCHAR)
	 +'ERROR_MESSAGE = ' + ERROR_MESSAGE();
END