MySQL stored procedure uses leave to implement the return syntax in MSSQL stored procedures

Source: Internet
Author: User
Tags mssql

DELIMITER $$

Use ' qrsoft_dyj_db ' $$

DROP PROCEDURE IF EXISTS ' Proc_withdraw_approve ' $$

CREATE PROCEDURE ' Proc_withdraw_approve ' (
In p_apply_id INT,
In P_handleuserid INT,
In P_handlestate INT,
In P_handletype INT,
In P_bankreceipt VARCHAR (200),
In P_apply_mark VARCHAR (2000),
In P_cash_serial VARCHAR (200))

Label_pro:begin
DECLARE Apply_userid INT;
DECLARE apply_balance DECIMAL;
DECLARE Apply_code VARCHAR (200);
DECLARE before_avabalance DECIMAL;

START TRANSACTION;

--Validate Audit record ID

IF p_apply_id = 0 Then
SELECT-1002 as Result_code;
ROLLBACK;
LEAVE Label_pro;
END IF;


--Verifying the status of audits
IF p_handlestate <> 2 && p_handlestate <> 3 Then
SELECT-1001 as Result_code;
ROLLBACK;
LEAVE Label_pro;
END IF;

--Query User ID withdrawal order number withdrawal amount
SELECT Applyrecord_ref_userid,
Applyrecord_balance,
Applyrecord_code into Apply_userid, Apply_balance, Apply_code
From Qr_apply_record
WHERE applyrecord_id = p_apply_id;

IF p_handlestate = 2 Then-audit success
--Update the withdrawal information Form
UPDATE Qr_apply_record SET
Applyrecord_handlestate = P_handlestate,
Applyrecord_handletime = Now (),
Applyrecord_handleuserid = P_handleuserid,
Applyrecord_handletype = P_handletype,
Applyrecord_bankreceipt = P_bankreceipt,
Applyrecord_mark = P_apply_mark
WHERE applyrecord_id = p_apply_id;

--Modify the accumulated withdrawal amount value in the User account information table
UPDATE qr_useraccount SET account_drawalsbalance = account_drawalsbalance + apply_balance
WHERE Account_ref_userid = Apply_userid;

ELSE--Audit failure
--Update the withdrawal information Form
UPDATE Qr_apply_record SET
Applyrecord_handlestate = P_handlestate,
Applyrecord_handletime = Now (),
Applyrecord_handleuserid = P_handleuserid,
Applyrecord_mark = P_apply_mark
WHERE applyrecord_id = p_apply_id;

--return withdrawal amount to account balance and add refund record
--1. Check the available Balance before user changes
SELECT account_avabalance to Before_avabalance from Qr_useraccount
WHERE Account_ref_userid = Apply_userid;

--2. Update Account Available Balance
UPDATE qr_useraccount SET account_avabalance = before_avabalance + apply_balance
WHERE Account_ref_userid = Apply_userid;

--3. Add a refund record
--Type of transaction (4: Refund (refund of travel Order request) 5: Refund (Withdrawal request denied refund))
--Trading Mode 1: Balance payment
INSERT into ' Qr_cash_record ' (' cashrecord_serial ',
' Cashrecord_ref_userid ',
' Cashrecord_beforebalance ',
' Cashrecord_balance ',
' Cashrecord_afterbalance ',
' Cashrecord_type ',
' Cashrecord_time ',
CASHRECORD_REF_ID,
Cashrecord_model)
VALUES (
P_cash_serial,
Apply_userid,
Before_avabalance,
Apply_balance,
(Before_avabalance + apply_balance), 5, Now (), p_apply_id, 1);

END IF;

COMMIT;

SELECT 1 as Result_code;

end$$

DELIMITER;

MySQL stored procedure uses leave to implement the return syntax in MSSQL stored procedures

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.