0
CREATE PROCEDURE [dbo].[USP_TESTEXCEPTION]
    @Param1 AS INT,
    @Param2 AS INT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE TABLE1 SET COLUMN1 = @Param1 

    SELECT @Param1, @Param2; // This select is causing problem.

    SELECT @Param/0;

    UPDATE TABLE2 SET COLUMN2 = @Param2
END

I am working on a Spring MVC project with SQL server 2012 back end. I am calling the following SP from my DAO layer and transaction is handled from application layer. Problem is this procedure is not raising any exception in application layer and for that partial transaction occurred(TABLE1 is being updated while TABLE2 is not). Why this SP is not raising exception in application layer?

Esty
  • 1,882
  • 3
  • 17
  • 36

1 Answers1

1

you will need to use try catch and xact_abort on

alter proc usp_test
as
begin
set nocount on
set xact_abort on
begin try
select 1
select 1/0
select 2
end try
begin catch
declare @errormessg varchar(max)
raiserror(@errormessg,16,1);
end catch
end
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • See this [link](http://stackoverflow.com/questions/34870909/sql-stored-procedure-exception-not-found-in-java). I have tried that already. But no luck. – Esty Feb 07 '16 at 09:00
  • all or nothing .more info here..http://stackoverflow.com/questions/1150032/what-is-the-benefit-of-using-set-xact-abort-on-in-a-stored-procedure – TheGameiswar Feb 07 '16 at 10:56