CODEWITHSUNDEEP
sql-serverexception
siddhu
siddhu

Reputation: 409

SQL Server Exception Handling

I am trying to understand what exactly is meant by TRY-CATCH does not catch compilation errors that occur in the same scope.

I am currently trying to set up exception handling in SQL Server and I came across this statement a couple of times, but don't quite understand what it means. I notice that if I have an INSERT statement, the exception handling works for PRIMARY KEY constraint errors, but it doesn't seem to work for INSERT statements that include an extra column.

Upvotes: 2

Views: 502

Answers (2)

Gautam Singh
Gautam Singh

Reputation: 19

you can use SQLEXCEPTION handler

here is an example

use TEST_SERVICE ;
DROP PROCEDURE IF EXISTS TEST_SERVICE.migrate_data;
DELIMITER //
CREATE PROCEDURE migrate_data()
Block1 : BEGIN
  declare loop_emp_check int default 0;
  declare emp_name varchar(100) default null;
  declare dept_id int default 0;
  declare error_message varchar(100) default null;
  declare error_code varchar(100) default null;
  
  -- cursor for employee data
  declare emp_cursor cursor for SELECT NAME, DEPARTMENT_ID FROM TEST_SERVICE.EMPLOYEE;
  
  -- handler declaration
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_emp_check = 1;

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS CONDITION 1 error_code = RETURNED_SQLSTATE, error_message = MESSAGE_TEXT;
    SELECT error_code, error_message;
    ROLLBACK;
  END;
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

  open emp_cursor;
  START TRANSACTION;
  emp_loop: loop
  
    -- fetching employee data
    fetch emp_cursor into emp_name, dept_id;
    
    -- loop termination condition
    if loop_emp_check = 1 then
    close emp_cursor;
    leave emp_loop;
    end if;
    
    -- inserting employee data
    INSERT INTO EMPLOYEE_V1(NAME, DEPARTMENT_ID) VALUES (emp_name, dept_id);
  END LOOP emp_loop;  
  COMMIT; -- commit once all the values which are migrated
END Block1;
//
delimiter ;

I recently wrote a blog on this.

Upvotes: -1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89051

it doesn't seem to work for INSERT statements that include an extra column.

Right, because TRY-CATCH does not catch compilation errors that occur in the same scope.

If your compilation error happens in a nested scope, your catch block will run.

Here's a simple example of the behavior:

create table t(id int)
go
begin try
   insert into t(id) values (1,2)
end try
begin catch

  select concat('catch block ', error_message())
end catch
go
begin try
   exec ('insert into t(id) values (1,2)');
end try
begin catch

  select concat('catch block ', error_message())
end catch

Upvotes: 2

Related Questions