datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to use nested TRY-CATCH to catch all errors?

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

 

Exam Prep 70-461
Exam 70-461