|
Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate how nesting TRY-CATCH-es catching errors at different scope.
-- SQL Server try catch nesting - nested try-catch - SQL nested TRY CATCH
USE AdventureWorks;
GO
SET NOCOUNT ON
CREATE TABLE Company(
CompanyID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50))
INSERT Company(Name) VALUES ('Ford Motor Company')
INSERT Company(Name) VALUES ('General Motors')
INSERT Company(Name) VALUES ('DaimlerChrysler')
CREATE TABLE ProfitCenter (
ProfitCenterID INT IDENTITY(1,1) PRIMARY KEY,
CompanyID INT,
Name VARCHAR(50))
ALTER TABLE [dbo].[ProfitCenter] WITH CHECK
ADD CONSTRAINT [FK_ProfitCenter_Company]
FOREIGN KEY([CompanyID])
REFERENCES [dbo].[Company] ([CompanyID])
GO
INSERT ProfitCenter (CompanyID, Name) VALUES ('1', 'Mercury')
INSERT ProfitCenter (CompanyID, Name) VALUES ('1', 'Ford')
INSERT ProfitCenter (CompanyID, Name) VALUES ('1', 'Lincoln')
INSERT ProfitCenter (CompanyID, Name) VALUES ('2', 'Chevrolet')
INSERT ProfitCenter (CompanyID, Name) VALUES ('2', 'Buick')
INSERT ProfitCenter (CompanyID, Name) VALUES ('2', 'Pontiac')
INSERT ProfitCenter (CompanyID, Name) VALUES ('2', 'Caddilac')
INSERT ProfitCenter (CompanyID, Name) VALUES ('2', 'Saturn')
INSERT ProfitCenter (CompanyID, Name) VALUES ('3', 'Mercedes')
INSERT ProfitCenter (CompanyID, Name) VALUES ('3', 'Chrysler')
CREATE TABLE PCDepartment (
PCDepartmentID INT IDENTITY(1,1) PRIMARY KEY,
ProfitCenterID INT,
Name VARCHAR(50))
ALTER TABLE [dbo].[PCDepartment] WITH CHECK
ADD CONSTRAINT [FK_PCDepartment_ProfitCenter]
FOREIGN KEY([ProfitCenterID])
REFERENCES [dbo].[ProfitCenter] ([ProfitCenterID])
INSERT PCDepartment (ProfitCenterID, Name) VALUES ('1', 'Sales')
INSERT PCDepartment (ProfitCenterID, Name) VALUES ('1', 'Production')
INSERT PCDepartment (ProfitCenterID, Name) VALUES ('1', 'Marketing')
INSERT PCDepartment (ProfitCenterID, Name) VALUES ('1', 'Service')
INSERT PCDepartment (ProfitCenterID, Name) VALUES ('1', 'Human Resources')
INSERT PCDepartment (ProfitCenterID, Name) VALUES ('1', 'Corporate')
INSERT PCDepartment (ProfitCenterID, Name) VALUES ('1', 'Development')
-- Population check
SELECT * FROM Company
/*
CompanyID Name
1 Ford Motor Company
2 General Motors
3 DaimlerChrysler
*/
SELECT * FROM ProfitCenter
/*
ProfitCenterID CompanyID Name
1 1 Mercury
2 1 Ford
3 1 Lincoln
4 2 Chevrolet
5 2 Buick
6 2 Pontiac
7 2 Caddilac
8 2 Saturn
9 3 Mercedes
10 3 Chrysler
*/
SELECT * FROM PCDepartment
/*
PCDepartmentID ProfitCenterID Name
1 1 Sales
2 1 Production
3 1 Marketing
4 1 Service
5 1 Human Resources
6 1 Corporate
7 1 Development
*/
-- First execution: delete bottom level of tree
-- Second execution: delete middle level of tree
-- Third execution: delete top level of tree
-- SQL TRY CATCH - SQL Server TRY-CATCH error handling
DECLARE @CompanyToDelete VARCHAR(50)
SET @CompanyToDelete = 'Ford Motor Company'
BEGIN TRY
DELETE FROM Company WHERE Name = @CompanyToDelete
PRINT 'Company '+@CompanyToDelete+' deleted successfully'
END TRY
BEGIN CATCH
PRINT 'Error Deleting Company Record'
BEGIN TRY
DELETE FROM ProfitCenter WHERE CompanyID =
(SELECT DISTINCT CompanyID FROM Company
WHERE Name = @CompanyToDelete)
PRINT 'ProfitCenter Deleted Successfully'
END TRY
BEGIN CATCH
PRINT 'Error Deleting ProfitCenter'
BEGIN TRY
DELETE FROM PCDepartment WHERE ProfitCenterId in
(SELECT DISTINCT ProfitCenterID
FROM ProfitCenter WHERE CompanyID =
(SELECT DISTINCT CompanyID FROM Company
WHERE Name = @CompanyToDelete))
PRINT 'PCDepartment Deleted Successfully'
END TRY
BEGIN CATCH
PRINT 'Error Deleting PCDepartment'
SELECT ERROR_NUMBER() ERNumber,
ERROR_SEVERITY() Error_Severity,
ERROR_STATE() Error_State,
ERROR_PROCEDURE() Error_Procedure,
ERROR_LINE() Error_Line,
ERROR_MESSAGE() Error_Message
END CATCH
END CATCH
END CATCH
GO
/* Messages
Error Deleting Company Record
Error Deleting ProfitCenter
PCDepartment Deleted Successfully
*/
-- Cleanup
DROP TABLE PCDepartment
DROP TABLE ProfitCenter
DROP TABLE Company
|
