Error Handling in SQL Server 2008 R2 Try Catch issue

Question

Error Handling in SQL Server 2008 R2 Try Catch issue

rahulsahay123

Old Hand

Points: 398
More actions
November 6, 2017 at 4:40 am

#326147

Scenario : we have a stored procedure named : usp_dummy
create procedure usp_dummy
as
begin try
begin tran t1
insert into output_table < this table is non existing in database>
select * from input_table < assumption : data structure of input_table & output_table is same>
commit;
end try
begin catch
if @@trans <> 0
rollback t1
end catch
Expected output : we have an error table where we want to insert the error message.
Present output : control is not coming to the catch block.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

What is your question here? You've made some statements about what your goals, but not asked a question.

As per the documentation on TRY...CATCH (Transact-SQL), however, the syntax you have used won't work. You have to do the TRY...CATCH outside of the SP for invalid objects. There is an example on that page on how to do it, but here's one anyway:

USE Sandbox;
GO
--Create a sample try proc with a TRY...CATCH inside
CREATE PROC sampletry AS
BEGIN TRY
  BEGIN TRANSACTION;
  
  INSERT INTO testtable
  SELECT *
  FROM Sampletable;
  
  PRINT ERROR_SEVERITY();
  COMMIT;
END TRY
BEGIN CATCH
  ROLLBACK;
  PRINT ERROR_MESSAGE();
  PRINT ERROR_SEVERITY();
  PRINT 'An Error has occured';
END CATCH
GO
--This will not enter the CATCH section
EXEC sampletry;
GO
--Clean up
DROP PROC sampletry;
GO
--Try a new proc, that simple tries to insert and select from non-existant tables
CREATE PROC sampletry AS
  INSERT INTO testtable
  SELECT *
  FROM Sampletable;
GO
--Do the TRY...CATCH outside of the SP. This works!
BEGIN TRY
  BEGIN TRANSACTION;
  EXEC sampletry;
  COMMIT;
END TRY
BEGIN CATCH
  PRINT 'An error has occured';
  ROLLBACK;
  PRINT ERROR_MESSAGE();
  PRINT ERROR_SEVERITY();
END CATCH
GO
--Clean up
DROP PROC sampletry;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

rahulsahay123 Old Hand Points: 398 More actions · Answer 2

i am not able to log the error message. control is not coming to the catch block

Thom A SSC Guru Points: 99133 More actions · Answer 3

rahulsahay123 - Monday, November 6, 2017 5:07 AM
i am not able to log the error message. control is not coming to the catch block

That's a statement, not a question. A question has a question mark (?) in it.

Anyway, back to the point, did you read my post and what the SQL Server documentation says? You can't use the syntax you have used. See my above example as a correct method.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sgmunson SSC Guru Points: 110640 More actions · Answer 4

rahulsahay123 - Monday, November 6, 2017 5:07 AM
i am not able to log the error message. control is not coming to the catch block

As Thom has been trying to say, you're not going to be able to, because your procedure isn't going to compile and execute when there's a missing object. That means you cannot use a TRY/CATCH block to catch that kind of error, from within the stored procedure that references a missing object. It MUST be done from OUTSIDE that procedure.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)