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.
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
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.
TRY-CATCH in that product. This code is for MySQL not for SQL Server and does not answer the question asked "what exactly is meant by TRY-CATCH does not catch compilation errors that occur in the same scope?"