none
Try - catch's in stored procedure RRS feed

  • Question

  • Hi,

    I have a procedure which uses more than one try – catch statement. The problem I have is that the procedure stops after the first catch. How can I include more than one try catch in my procedure?

    Here is an example part of my procedure:

    use myTestDB;

    go

     

    if OBJECT_ID('dbo.proc_CreateColumns') is not null begin

          drop proc dbo.proc_CreateColumns;

    end;

    go

     

    create proc dbo.proc_CreateColumns as

     

    -- 1 --

    BEGIN TRY

          alter table abgverkaeufe

          add ID int NULL;

    END TRY

    BEGIN CATCH

    END CATCH;

    go

    -- 2 --

    BEGIN TRY

          alter table abgverkaeufe

          add FiNr int NULL;

    END TRY

    BEGIN CATCH

    END CATCH;

    go

    -- 3 --

    BEGIN TRY

          alter table abgverkaeufe

          add KasNr int NULL;

    END TRY

    BEGIN CATCH

    END CATCH;

    go

     -- 4 --

    BEGIN TRY

          alter table abgverkaeufe

          add BoNr int NULL;

    END TRY

    BEGIN CATCH

    END CATCH;

    go

     -- 5 --

    BEGIN TRY

          alter table abgverkaeufe

          add BonPos int NULL;

    END TRY

    BEGIN CATCH

    END CATCH;

    Very grate full for any help.

    Wayne

    Monday, February 22, 2010 5:54 PM

Answers

All replies

  • why do you need all that?
    You can just put everything in one TRY Catch block if you are not doing anything in your Catch blocks, and you can use RAISEERROR if you still need to generate any error messages within your code.
    Abdallah El-Chal, PMP, ITIL, MCTS
    Monday, February 22, 2010 6:04 PM
  • Hello,

    I believe what you are looking for is nested transactions to ensure that all commands are either committed upon success or rolled back on error.

    With that, you would typically wrap a single TRY/CATCH around the entire code and rollback in the CATCH  block.

    Adam
    Dibble and dabble but please don't babble.
    • Edited by Adam_Turner Monday, February 22, 2010 6:37 PM
    Monday, February 22, 2010 6:08 PM
  • Hello,
    I need more than one try catch block because, I do not know if or how many of the columns I need to insert. May be I only need to add one column but may be 100. If I use a single try catch and the first column fails then would not the catch be executed without trying to insert the other columns?

    Wayne
    Monday, February 22, 2010 6:23 PM
  • So, if you try to insert one record and it fails, you still want to continue the process without knowing that an error has occured?
    Abdallah El-Chal, PMP, ITIL, MCTS
    Monday, February 22, 2010 6:27 PM
  • Yes, the idea is to update a existing db without knowing the state it is in at the moment the update is executed.
    Monday, February 22, 2010 6:40 PM
  • Well,

    You can always nest the next try inside the catch:

    -- 1 --

    BEGIN TRY

          alter table abgverkaeufe

          add ID int NULL;

    END TRY

    BEGIN CATCH

       -- 2 --

       BEGIN TRY

             alter table abgverkaeufe

             add FiNr int NULL;

       END TRY

       BEGIN CATCH
          -- 3 --

       END CATCH;
    END

    go

    go

    CATCH;
    Dibble and dabble but please don't babble.
    Monday, February 22, 2010 10:56 PM
  • Yes I could, but if the try executes then the catch will not and so the rest of the columns will not be inserted if they do not exist.
    What I need to do is to update more than one table, inserting columns if they do not exist in the tables.
    The code above in post one does work as a stand alone statement but not as a procedure because it only exepts one try - catch block.

    Wayne
    Tuesday, February 23, 2010 6:16 AM
  • Why dont you alter the prcedure to accept the columns to be created as parameters and alter the whole table in one short through dynamic sql.

    If there is problem with a particular column which raises exception,whole transaction gets failed.I think that is better.Because it is difficult for u to know which are the columns that got added recently otherwise!
    Busy in learning!!
    Tuesday, February 23, 2010 6:27 AM

  • The procedure doesnt make much of a sense to me. But still if you want to do it the better way i could think of is to check to see if the column to add is already existing or not before adding it. 

     CREATE PROC dbo.proc_CreateColumns 
     AS
     BEGIN   
        BEGIN TRY    
          -- 1
          IF COLUMNPROPERTY( OBJECT_ID('abgverkaeufe'),'ID','ColumnID') IS NULL
           ALTER TABLE abgverkaeufe ADD ID INT NULL;
          
          -- 2
          IF COLUMNPROPERTY( OBJECT_ID('abgverkaeufe'),'FiNr','ColumnID') IS NULL
           ALTER TABLE abgverkaeufe ADD Finr INT NULL;
         
          -- 3
          IF COLUMNPROPERTY( OBJECT_ID('abgverkaeufe'),'KasNr','ColumnID') IS NULL
           ALTER TABLE abgverkaeufe ADD KasNr INT NULL;
           
        END TRY
        BEGIN CATCH
          -- handle the error/ which could cause any alter to fail
        END CATCH   
     END 


    Ranjith | My Blog
    Tuesday, February 23, 2010 8:23 AM
  • Hi,
    Yes , the above would work fine. But so you understand what I am trying to do.  I have 14 db's each with over 1000 colums and 136 tables filled with data. The db's are a little different on each machine and I need to change them so they are all exacly the same or have a standard set of necessary columns.
    I hope this makes sence to you. The columns are just the first part of the update.

    Wayne
    Tuesday, February 23, 2010 4:04 PM
  • Yeah got it. In that case  the above method works well.


    Ranjith | My Blog
    • Marked as answer by waynecod Tuesday, February 23, 2010 6:22 PM
    Tuesday, February 23, 2010 5:55 PM