How we can use transaction in SQL?

When creating stored procedures in SQL, it is important to ensure data integrity and consistency. Transactions play a crucial role in achieving these goals.

To use transactions in SQL stored procedures, you can follow these steps:

1. Begin the transaction: Use the BEGIN TRANSACTION statement to start a transaction. This marks the beginning of a logical unit of work.

2. Perform the necessary operations: Within the stored procedure, execute the required SQL statements to manipulate data or perform other operations.

3. Commit or rollback the transaction: After executing the necessary operations, you have two options. If everything is successful and you want to save the changes permanently, use the COMMIT statement to commit the transaction. If any error occurs or you want to discard the changes, use the ROLLBACK statement to undo the transaction.

Here's an example of a stored procedure that uses transactions:

CREATE PROCEDURE dbo.MyProcedure
AS
BEGIN
    BEGIN TRANSACTION

    -- Perform operations
    -- ...

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK
        RETURN
    END

    COMMIT
END

In this example, the BEGIN TRANSACTION statement starts the transaction, and the COMMIT statement commits the changes if no errors occur. If an error occurs, the ROLLBACK statement is executed to undo the transaction.

Using transactions in SQL stored procedures helps ensure data consistency and allows you to handle errors effectively. It provides a way to group multiple SQL statements into a single logical unit of work, making your code more robust and reliable.

1. BEGIN TRANSACTION: Starts a new transaction.
2. BEGIN TRY / BEGIN CATCH: Wraps SQL statements in a try-catch block to catch errors.
3. TRY: Executes SQL statements (e.g., INSERT, UPDATE) within the transaction.
4. COMMIT TRANSACTION: If all statements succeed, commits changes permanently.
5. CATCH: If an error occurs, rolls back the transaction with ROLLBACK TRANSACTION to maintain data consistency.

1. BEGIN TRANSACTION: Initiates a new transaction.

2. BEGIN TRY and BEGIN CATCH: Wraps the SQL statements within a try-catch block. This allows catching errors that occur within the transaction.

3. Within the TRY block, execute your SQL statements. These could include INSERT, UPDATE, DELETE, etc., on one or multiple tables.

4. If all statements execute successfully (TRY block), commit the transaction using COMMIT TRANSACTION. This makes the changes permanent in the database.

5. If an error occurs during the execution (CATCH block), rollback the transaction using ROLLBACK TRANSACTION. This reverts all changes made within the transaction, maintaining data consistency.

CREATE PROCEDURE AddNewProduct
    @ProductCode VARCHAR(20),
    @ProductName VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- Begin the transaction
        BEGIN TRANSACTION;

        -- Insert the new product into the Products table
        INSERT INTO Products (ProductCode, ProductName)
        VALUES (@ProductCode, @ProductName);

        -- Commit the transaction if successful
        COMMIT TRANSACTION;

        SELECT 'New product added successfully' AS Result;
    END TRY
    BEGIN CATCH
        -- If an error occurs, rollback the transaction
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        -- Handle the error (log it, raise an exception, etc.)
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END

Hi Amrita,

do refer below link

https://www.c-sharpcorner.com/UploadFile/84c85b/understanding-transactions-in-sql-server/

To use transactions in SQL while creating stored procedures, you can follow these steps:

1. Start a Transaction: Begin the transaction by using the `BEGIN TRANSACTION` statement. This sets up a transaction context and ensures that all subsequent statements within the stored procedure are executed within this transaction.

```sql
BEGIN TRANSACTION;
```

2. Perform Database Operations: Write the SQL statements that perform the required database operations within the stored procedure. These operations can include inserting, updating, or deleting data from one or more tables.

```sql
-- Example: Inserting data
INSERT INTO TableName (Column1, Column2)
VALUES (Value1, Value2);

-- Example: Updating data
UPDATE TableName
SET Column1 = NewValue
WHERE Condition;

-- Example: Deleting data
DELETE FROM TableName
WHERE Condition;
```

3. Handle Errors: If any of the database operations within the transaction encounter an error, you can use error handling mechanisms to handle or rollback the transaction. This ensures that the changes made by previous statements are undone.

```sql
-- Example: Error handling and rolling back the transaction
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
    -- Handle the error or raise an error message
END
```

4. Commit the Transaction: If all the database operations within the transaction are successful and you want to permanently apply the changes, use the `COMMIT TRANSACTION` statement. This commits the transaction and makes the changes permanent.

```sql
COMMIT TRANSACTION;
```

5. Rollback the Transaction (optional): If you encounter an error or want to discard the changes made within the transaction, you can use the `ROLLBACK TRANSACTION` statement. This rolls back the transaction and undoes all the changes made within it.

```sql
ROLLBACK TRANSACTION;
```

By using transactions in your stored procedures, you can ensure that a group of related database operations either succeeds as a whole or fails as a whole. This helps maintain data integrity and consistency in your database.

When creating stored procedures in SQL, you can use transactions to ensure that a set of database operations either all succeed or all fail together. Transactions help maintain data integrity and consistency by providing an all-or-nothing approach to executing multiple SQL statements. Here's how you can use transactions in SQL while creating stored procedures:

1. Begin the transaction: Use the BEGIN TRANSACTION statement to start a transaction. This marks the beginning of a block of SQL statements that should be executed as a single unit.

BEGIN TRANSACTION;

2. Execute SQL statements: Write the necessary SQL statements within the stored procedure that perform the required database operations, such as inserts, updates, or deletes. These statements will be part of the transaction.

-- Example SQL statements within the stored procedure
INSERT INTO table1 (column1, column2) VALUES (value1, value2);
UPDATE table2 SET column1 = value1 WHERE condition;

3. Check for errors and commit or rollback: After executing the SQL statements, check for any errors. If an error occurs, you can rollback the transaction using the ROLLBACK statement. If there are no errors and the operations should be committed to the database, use the COMMIT statement.

IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    RETURN; -- or handle the error
END

COMMIT;

4. End the transaction: Close the transaction by using the COMMIT statement. This saves all the changes made within the transaction to the database permanently.

COMMIT;

By encapsulating your database operations within a transaction, you ensure that either all the changes are applied successfully, or none of them are applied at all. This helps maintain data consistency and recoverability in case of errors or interruptions.

Thanks

Transactions in SQL can be used to ensure that a group of SQL statements are treated as a single unit of work that is either completed in its entirety or rolled back if an error occurs.

To use transactions in a stored procedure in SQL, you can enclose the group of SQL statements that should be executed as a single unit of work within a BEGIN TRANSACTION and COMMIT TRANSACTION statements.

CREATE PROCEDURE [dbo].[exampleProcedure]
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;

    -- SQL statements to be executed as a single unit of work go here
    INSERT INTO table1 (column1, column2) VALUES ('value1', 'value2');
    UPDATE table2 SET column1 = 'new value' WHERE column2 = 'value3';

    COMMIT TRANSACTION;
END

In this example, the BEGIN TRANSACTION statement starts a new transaction, and the COMMIT TRANSACTION statement commits the transaction if all the SQL statements within the transaction were executed successfully. If an error occurs during the execution of the SQL statements within the transaction, the transaction will be rolled back to its original state before the BEGIN TRANSACTION statement was executed.

Using transactions in stored procedures can help ensure the integrity of the data in a database.  

Hi Amrita,

A transaction is the logical work unit that performs a single activity or multiple activities in a database. SQL Server can operate 3 different transactions modes:
1. Autocommit Transaction
2. Implicit transaction
3. Explicit transaction

I'm sure you are looking for explicit transaction type in SQL. He is the script to achive this:

ALTER PROCEDURE [dbo].[DeleteStudentTransaction]
@Id INT
AS
BEGIN TRY
    BEGIN TRANSACTION
        DELETE FROM Student WHERE Id=@Id
        -- some other script
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
END CATCH

Hello Amrita,

Please refer below link.

https://www.sqlshack.com/transactions-in-sql-server-for-beginners/

https://techfunda.com/howto/192/transaction-in-stored-procedure#:~:text=If%20we%20have%20more%20than,use%20transaction%20in%20stored%20procedure.

Thanks

A transaction is a single unit of work that typically contains multiple T-SQL statements.

If a transaction is successful, the changes are committed to the database. However, if a transaction has an error, the changes have to be rolled back.

When executing a single statement such as INSERT, UPDATE, and DELETE, SQL Server uses the auto-commit transaction. In this case, each statement is a transaction.

The below sequence of statements for starting a transaction explicitly and committing it:

-- start a transaction
BEGIN TRANSACTION;

-- other statements

-- commit the transaction
COMMIT;

Thanks

like this

CREATE PROCEDURE [dbo].[InsertPersonalDetailsAndAccount]
       -- Add the parameters for the stored procedure here
       @FirstName varchar(50),
       @LastName varchar(50),
       @Age smallint,
       @Active bit,
       @Salary money,
       @PPFDeduction money
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

       BEGIN TRAN
              BEGIN TRY
                     -- Insert into PersonalDetails table first
                     INSERT INTO PersonalDetails
                            (FirstName, LastName, Age, Active)
                     VALUES
                            (@FirstName, @LastName, @Age, @Active)

                     DECLARE @pdId int
                     SET @pdId = SCOPE_IDENTITY()
                     -- now insert into Accounts table
                     INSERT INTO Accounts
                            (Salary, PPFDeduction, PersonalDetailsId)
                     VALUES
                            (@Salary, @PPFDeduction, @pdId)
                     
              -- if not error, commit the transcation
              COMMIT TRANSACTION
       END TRY
       BEGIN CATCH
              -- if error, roll back any chanegs done by any of the sql statements
              ROLLBACK TRANSACTION
       END CATCH
END

Credit :- https://techfunda.com/howto/192/transaction-in-stored-procedure

Hi Amrita,

Please refer this link,

https://www.c-sharpcorner.com/UploadFile/87b416/sql-transaction/

https://www.mssqltips.com/sqlservertip/4897/handling-transactions-in-nested-sql-server-stored-procedures/