CODEWITHSUNDEEP
sqlexceptiontry-catch
Chris
Chris

Reputation: 13

Try..Catch SQL Question

Is there any way of know which of my update or insert statements executed the catch block by failing

Upvotes: 1

Views: 578

Answers (6)

abatishchev
abatishchev

Reputation: 100258

How about to surround your update and insert statements with different try-catch blocks?

Also you can check SELECT ERROR_MESSAGE(), ERROR_STATE() to determine what does throw current exception

Upvotes: 1

KM.
KM.

Reputation: 103587

try something like this:

Declare @Where varchar(50)

BEGIN TRY
    SET @Where='1st update'
    UPDATE....
    SET @Where=NULL

    ...

    SET @Where='2nd update'
    UPDATE....
    SET @Where=NULL

    ...

    SET @Where='1 insert'
    INSERT....
    SET @Where=NULL


END TRY
BEGIN CATCH
    PRINT @Where

END CATCH

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

Start by creating a table variable Insert the steps you have run or the error messages into the table varaiable as you go through the proc. After the rollback inthe catch, the table variable is still available, then use it to insert into your logging table.

Upvotes: 3

user184975
user184975

Reputation: 1

One thing that comes to mind is logging an error code to the event viewer right after its been executed.

For example

Try err_code=1 --Query 1-- err_code=2 --Query 2-- . . . Catch -- log err_code to event viewer--

Then by viewing the event viewer you will see the err_code of the last succesfull query.

hope it helps

Upvotes: 0

Guffa
Guffa

Reputation: 700292

The stack dump can contain some information, like what line the error occured on, but that's not directly useful.

The simplest is probably to set a variable before each query, then you can check it's value in the catch.

Upvotes: 0

miku
miku

Reputation: 188014

Oracle PL/SQL has exception handling: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm.

Otherwise you can use programming language exception handling facilities.

Upvotes: 0

Related Questions