Correct use of transactions in SQL Server

by

Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

      INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
      VALUES ('Tidd130', 130), ('Tidd230', 230)

      UPDATE [Test].[dbo].[T1]
      SET [Title] = N'az2' ,[AVG] = 1
      WHERE [dbo].[T1].[Title] = N'az'

      COMMIT TRANSACTION [Tran1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [Tran1]

  END CATCH

More Related Contents:

  1. Generate a resultset of incrementing dates in TSQL
  2. T-SQL Cast versus Convert
  3. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  4. What is the difference between using a cross join and putting a comma between the two tables?
  5. What does =* mean?
  6. Why do you create a View in a database?
  7. Need to list all triggers in SQL Server database with table name and table’s schema
  8. Know relationships between all the tables of database in SQL Server
  9. SQL Server search using like while ignoring blank spaces
  10. SQL where clause odd behavior [closed]
  11. How do I escape a single quote in SQL Server?
  12. Simple way to transpose columns and rows in SQL?
  13. Only inserting a row if it’s not already there
  14. Unpivot with column name
  15. Selecting data from two different servers in SQL Server
  16. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  17. Most efficient way in SQL Server to get date from date+time?
  18. Convert varchar into datetime in SQL Server
  19. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  20. Splitting the string in sql server
  21. How do I group on continuous ranges
  22. join comma delimited data column
  23. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  24. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  25. Best equivalent for IsInteger in SQL Server
  26. Comma separated values in a database field
  27. Can SQL Server Express LocalDB be connected to remotely?
  28. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  29. COALESCE Function in TSQL
  30. Should I commit or rollback a read transaction?

Leave a Comment