locked
t-sql 2012 using error handling logic RRS feed

  • Question

  • In a t-sql 2012 database, I am planning to run the following sql unless you make suggestions on how to change the sql.
    This is the first time that I have used the following:
    1. rollback and commit, and
    2. try and catch blocks looking for potential errors that can get generated.
    BEGIN TRANSACTION;
      BEGIN TRY
      insert into O.dbo.Stu
      select  personID
          ,enrollmentID
          ,attributeID = 3374
          ,value
          ,date
          from O.dbo.Stu
      where attributeID = 997
      insert into O.dbo.Stu
      select  personID
          ,enrollmentID
          ,attributeID = 3373
          ,value
          ,date
          from O.dbo.Stu
      where attributeID = 996
      Insert into O.dbo.Stu
       Select c1.personID
              ,c1.enrollmentID
              ,attributeID=case when c2.value = 'N' then 3371 else 3370 end
              ,c1.value
            ,c1.date  
      from O.dbo.Stu C1
      LEFT JOIN O.dbo.Stu c2
         on  c2.personID=c1.personID
          and (c2.date = c1.date or cast(c2.date as date) =  cast(c1.date as date))
       and C2.attributeID= 997
      where  C1.attributeID = 1452
      END TRY
      BEGIN CATCH
      If @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
      END CATCH;
     If @@TRANCOUNT > 0
         COMMIT TRANSACTION;
    GO
    There are basically 3 insert statements.
    Thus would you change the t-sql that I listed above where you have a better method on how to handle the
    situation?
    Thursday, January 26, 2017 10:45 PM

Answers

  • Do they all need to be completed at once, or are they independent?

    What you wrote will work, but requires all 3 to be successful.  Do you really need that?

    Friday, January 27, 2017 6:01 PM

All replies

  • Hi Wendy,

    I read this great article on error handling in transactions. I hope it gives you a lead on various options you have to take the best route.

    Cheers,

    Sunit


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.

    Thursday, January 26, 2017 11:59 PM
  • Thanks for the link! However I would like more of a specific reference on what to use. What would you recommend?
    Friday, January 27, 2017 5:05 PM
  • Do they all need to be completed at once, or are they independent?

    What you wrote will work, but requires all 3 to be successful.  Do you really need that?

    Friday, January 27, 2017 6:01 PM