Practical stored procedures for bank transfers and storage of sequential numbers, and stored procedures for bank transfers

Practical stored procedures for bank transfers and storage of sequential numbers, and stored procedures for bank transfers

Bank Transfer Stored Procedure

USE [BankInfor] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ongoalter procedure [dbo]. [Transfer] (@ inAccount int, @ outAccount int, @ amount float) as declare @ totalDeposit float; begin select @ totalDeposit = total from Account where AccountNum = @ outAccount; if @ totalDeposit is null begin rollback; print 'transfer-out account does not exist or there is no deposit in the account 'Return; end if @ totalDeposit <@ amount begin rollback; print 'the balance is insufficient, 'Return; end update Account set total = total-@ amount where AccountNum = @ outAccount; update Account set total = total + @ amount where AccountNum = @ inAccount; print 'transfer successful! 'Commit; end;

Storage Process for serial number generation

If exists (select 1 from sysobjects where id = OBJECT_ID ('getserialno') and xtype = 'P') drop proc GetSerialNogoCreate procedure [dbo]. [GetSerialNo] (@ sCode varchar (50) as begin Declare @ sValue varchar (16), @ dToday datetime, @ sQZ varchar (50) -- This indicates the prefix Begin Tran Begin Try -- lock this record. Many people use lock to lock the record. At the beginning, only one update statement can be executed. -- in the same thing, after the update statement is executed, the lock Update SerialNo set sValue = sValue where sCode = @ sCode Select @ sValue = sValue From SerialNo where sCode = @ sCode Select @ sQZ = sQZ From SerialNo where sCode = @ sCode -- there is no record in the factor table, insert initial value If @ sValue is null Begin Select @ sValue = convert (bigint, convert (varchar (6), getdate (), 12) + '123 ') update SerialNo set sValue = @ sValue where sCode = @ sCode end else Begin -- Select @ dToday = substring (@ sValue,) is not recorded in the factor table -- if the date is equal, add 1 If @ dToday = convert (varchar (6), getdate (), 12) Select @ sValue = convert (varchar (16), (convert (bigint, @ sValue) + 1) else -- if the date is not equal, assign a value to the date first. The serial number starts from 1 Select @ sValue = convert (bigint, convert (varchar (6), getdate (), 12) + '123 ') update SerialNo set sValue = @ sValue where sCode = @ sCode End Select result = @ sQZ + @ sValue Commit Tran End Try Begin Catch Rollback Tran Select result = 'error' End Catch end select * from SerialNoselect convert (varchar (6 ), getdate (), 12) + '123'