ตอนที่ 9 : การใช้ Try Catch บน Stored Procedure (SQL Server : Stored Procedure)

BEGIN TRY
	// Statement
END TRY
BEGIN CATCH
	// Exception
END CATCH

		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;

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[myStoredProcedure]    Script Date: 12-Sep-15 12:39:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[myStoredProcedure]
	@pOne	DECIMAL(10,2),
	@pTwo	DECIMAL(10,2)
AS
BEGIN
	
	BEGIN TRY
		SELECT @pOne/@pTwo
	END TRY
	BEGIN CATCH
		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;
	END CATCH

END

EXEC myStoredProcedure '100','0'

USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[insertCustomer]    Script Date: 10/23/2015 22:41:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[insertCustomer]
	@pCustomerID	VARCHAR(4),
	@pName			VARCHAR(50),
	@pEmail			VARCHAR(50),
	@pCountryCode	VARCHAR(2),
	@pBudget		DECIMAL(18,2),
	@pUsed			DECIMAL(18,2),
	@pResult		INT OUTPUT,
	@pMessage		VARCHAR(500) OUTPUT
AS
BEGIN

	BEGIN TRY
		-- insert customer
		INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
		SET @pResult = @@ROWCOUNT;
		SET @pMessage = 'Insert Data Successfully';
	END TRY
	BEGIN CATCH
		SET @pResult = 0;
		SELECT @pMessage = ERROR_MESSAGE();
	END CATCH

END