Procedure with transaction inside

Procedure with transaction inside

Medium SQL Stored Procedures 21 views
Explanation Complexity

Problem Statement

Create sp_transfer_funds(p_from, p_to, p_amt) to transfer money between two accounts. If the sender does not have enough balance, rollback the transaction so nothing changes.

Input Format

SQL procedure

Output Format

Procedure created

Example

Accounts(acc_id, balance)
Procedure created

Constraints

MySQL-style procedure syntax

Input / Output Format

Input Format
SQL procedure
Output Format
Procedure created
Constraints
MySQL-style procedure syntax

Examples

Input:
Accounts(acc_id, balance)
Output:
Procedure created

Example Solution (Public)

SQL 
CREATE PROCEDURE sp_transfer_funds(IN p_from INT, IN p_to INT, IN p_amt DECIMAL(10,2)) BEGIN START TRANSACTION; UPDATE Accounts SET balance = balance - p_amt WHERE acc_id = p_from AND balance >= p_amt; IF ROW_COUNT() = 0 THEN ROLLBACK; ELSE UPDATE Accounts SET balance = balance + p_amt WHERE acc_id = p_to; COMMIT; END IF; END;

Official Solution Code

CREATE PROCEDURE sp_transfer_funds(IN p_from INT, IN p_to INT, IN p_amt DECIMAL(10,2)) BEGIN START TRANSACTION; UPDATE Accounts SET balance = balance - p_amt WHERE acc_id = p_from AND balance >= p_amt; IF ROW_COUNT() = 0 THEN ROLLBACK; ELSE UPDATE Accounts SET balance = balance + p_amt WHERE acc_id = p_to; COMMIT; END IF; END;
Please login to submit solutions.
Editor
Output

                                        

                                    

                                    

                                        

                                                                                    
Please login to submit solutions.