-- Example 128 --      

 

USE EBusiness

GO

 

-- STEP 01 -- INSERT Sample Records --

 

INSERT INTO DEPT VALUES (123,'EDP Department')

 

INSERT INTO EMP VALUES (151,'Employee 501','12-04-2002',1500.50,'101-1234567',123)

INSERT INTO EMP VALUES (152,'Employee 502','12-04-2002',1500.50,'101-1234567',123)

INSERT INTO EMP VALUES (153,'Employee 503','12-04-2002',1500.50,'101-1234567',123)

INSERT INTO EMP VALUES (154,'Employee 504','12-04-2002',1500.50,'101-1234567',123)

INSERT INTO EMP VALUES (155,'Employee 505','12-04-2002',1500.50,'101-1234567',123)

GO

 

-- STEP 02 -- Create Stored Procedure with TRY ... CATCH Statements --

 

CREATE PROCEDURE dbo.usp_DeleteDepartmentEmployeeDetail

@Dept_Code int

AS

SET NOCOUNT ON;

BEGIN TRY

      BEGIN TRANSACTION

           -- Delete Child Table's Records - EDP Department's Employee Records --

           DELETE FROM dbo.EMP WHERE Dept_Code = @Dept_Code

 

           -- Delete Parent Table's Records - EDP Department Record --

           DELETE FROM dbo.DEPT WHERE Dept_Code = @Dept_Code

      COMMIT

END TRY

 

BEGIN CATCH

      -- Determine if an error occurred.

      IF @@TRANCOUNT > 0

           ROLLBACK

           -- Return the error information.

           DECLARE @ErrorMessage nvarchar(4000),   @ErrorSeverity int;

           SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();

           RAISERROR(@ErrorMessage, @ErrorSeverity, 1);

END CATCH;

GO

 

-- STEP 04 -- Display particular Department and it's Employee Information --

 

SELECT * FROM DEPT WHERE Dept_Code = 123

 

SELECT * FROM EMP WHERE Dept_Code = 123

 

-- STEP 05 -- Delete particular Department and it's Employee Information --

 

EXEC usp_DeleteDepartmentEmployeeDetail 123

 

-- STEP 06 -- Display particular Department and it's Employee Information --

 

SELECT * FROM DEPT WHERE Dept_Code = 123

 

SELECT * FROM EMP WHERE Dept_Code = 123