Stop TSQL from raising errors to caller

  • Hi everyone:

    I am writing a stored procedure en SQL Server 2000, this procedure has one INSERT statement that can fail by violating a primary key of the table it is inserting data to. I am catching @@Error value, but this does not prevent SQL from raising the exception to the caller. I would like the exception not to be raised because I am handling it into the stored procedure itself and when this stored procedure is called from a DTS, this raised exceptions cause the DTS to stop...

    Any hints will be appreciated!

  • How is the stored procedure inserting the data? Is it row by row or from another table?

  • Hi:

    I am doing it row by row, but if inserting in a batch is better I would give it a try.

    Thanks

  • Can you post the code for your stored procedure?

  • Ok, I just changed some columns and table names for secutiry reasons:

    DECLARE CURSOR_TEMP CURSOR FOR

    SELECT Key1, Key2, Data1

    FROM INT_Table

    OPEN CURSOR_TEMP

    FETCH NEXT FROM CURSOR_TEMP

    INTO @Key1, @Key2, @Data1

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @ERROR = 0

    INSERT INTO [INT_Table2](Key1, Key2, data1)

    VALUES(@Key1, @Key2, @Data1)

    SET @ERROR = @@ERROR

    IF @ERROR <> 0

    BEGIN

    PRINT 'ERROR:' + CAST(@ERROR AS VARCHAR)

    GOTO CONTROL_ERROR

    END

    CONTROL_ERROR:

    -- Here is where I handle the error but the error is still raised and the DTS aborts and reports the task as failed.

    IF @ERROR <> 0

    BEGIN

    UPDATE INT_Table

    SET

    MESSAGE = ''

    WHERE

    Key1 = @Key1 AND Key2 = @Key2

    END

    FETCH NEXT FROM CURSOR_TEMP

    INTO @Key1, @Key2, @Data1

    END

    CLOSE CURSOR_TEMP

    DEALLOCATE CURSOR_TEMP

    Thanks for any help.

    By now I would do it the old way, check if there is another row into the table with the same primary key values before inserting it.

  • As you are pulling the data from a table, I'm at a loss as to why you are using a cursor for this process. Below is the code I would use to replace the code currently in your stored procedure.

    UPDATE INT_Table2 SET

    MESSAGE = ''

    FROM

    INT_Table2 t2

    INNER JOIN INT_Table t1

    on (t2.Key1 = t1.Key1

    t2.Key2 = t1.Key2)

    INSERT INTO INT_Table2 (Key1, Key2, Data1)

    SELECT

    t1.Key1,

    t1.Key2,

    t1.Data1

    FROM

    INT_Table t1

    LEFT OUTER JOIN INT_Table2 t2

    ON (t1.Key1 = t2.Key1

    AND t1.Key2 = t2.Key2)

    WHERE

    t2.Key1 IS NULL

    No cursor, no error handling needed. Test as I have no test tables or data to do that myself.

  • Thanks for the reply, even it did not answer my original question it help me somehow.

    Good way to solve the problem.

  • If you need an answer on your question here it is:

    "There is no way".

    If you then want to know how to do things properly in SQL read above.

    _____________
    Code for TallyGenerator

  • Jportelas (11/28/2008)


    Thanks for the reply, even it did not answer my original question it help me somehow.

    Good way to solve the problem.

    To take what Sergiy said and expand a little, there is no way in SQL Server 2000. If you were running SQL Server 2005 you could write it using try/catch blocks, or SQL Server 2008 you could use the new MERGE statement.

    Problem is, you were using a RBAR (Row By Agonizing Row) solution where a set based solution was available.

  • Just so we answer the original question - use the RETURN statement to return the status back to the caller.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams (11/28/2008)


    Just so we answer the original question - use the RETURN statement to return the status back to the caller.

    That doesn't really answer the question. He wanted to trap and handle the error in the stored procedure AND not have it returned to the caller.

  • Sure it does - handle the error and RETURN 0 to the caller. Stored procedure will return to caller a status of successful completion.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams (11/28/2008)


    Sure it does - handle the error and RETURN 0 to the caller. Stored procedure will return to caller a status of successful completion.

    Did you read OP?

    I would like the exception not to be raised because I am handling it into the stored procedure itself and when this stored procedure is called from a DTS, this raised exceptions cause the DTS to stop...

    Errors of such severity will ALWAYS stop execution.

    End procedure will never come to RETURN. Such errors stop execution immediately and return error codes.

    But it does not help OP - he wants it does not stop but go ahead with error message.

    _____________
    Code for TallyGenerator

  • Even with the 2005 TRY/CATCH, you can't prevent EVERY error from bubbling back up. Some failures are so egregious - they're going to bust you up and that's it.

    You can get pretty far by having TRY/CATCH in both the calling code layer (the code being sent by the client) and the in code being called, but even then, some things will get through....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Thanks Sergyi, Matt and Jeffrey, so there is no way to achieve this in T-SQL for SQL Server 2000?

    Seems like trying to cover all the failure scenarios is the only way.

Viewing 15 posts - 1 through 15 (of 20 total)

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