开发者

Try..Catch SQL Question

开发者 https://www.devze.com 2023-01-01 10:58 出处:网络
Is there any way of know which of my update or insert statements executed 开发者_StackOverflowthe catch block by failingStart by creating a table variable

Is there any way of know which of my update or insert statements executed 开发者_StackOverflowthe catch block by failing


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.


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


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.


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.


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


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
0

精彩评论

暂无评论...
验证码 换一张
取 消