Practical bank transfer stored procedures and serial number generation stored procedures _mssql

Bank Transfer stored procedures

Use [bankinfor]
go
set ANSI_NULLS on Go Set QUOTED_IDENTIFIER on Go
ALTER PROCEDURE [dbo].[ Transfer] (@inAccount int, @outAccount int, @amount float) as
declare
  @totalDeposit float;
  The Begin
  Select @totalDeposit =total from the account where accountnum= @outAccount;
  If @totalDeposit is null
  begin
  rollback;
  print ' Turn out the account does not exist or there is no deposit in the account ' return
  ;
  End
  If @totalDeposit < @amount
  begin
  rollback;
  print ' balance not enough to operate ' 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

Stream number generation stored procedure

if exists (select 1 from sysobjects where id=object_id (' Getserialno ') and Xtype= ' P ") drop proc Getserialno go Create proce dure [dbo].  
[Getserialno] (@sCode varchar) as begin Declare @sValue varchar (), @dToday datetime, @sQZ varchar (50)-This represents the prefix Begi N Tran Begin Try-Lock the record, a lot of people lock it, start with an update--in the same thing, the lock update Serialno SE is started after the UPDATE statement is executed.  T 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 the initial value If @sValue is null Begin Select @sValue = CONVERT (bigin T, convert (varchar (6), GETDATE (), () + ' 000001 ') Update Serialno set svalue= @sValue where scode= @sCode end Els The Select @dToday = substring (@sValue, 1,6)--If the date is equal, add 1 if @dToday = Convert  
     (varchar (6), GETDATE (), 12)    Select @sValue = CONVERT (varchar) (CONVERT (bigint, @sValue) + 1) Else   -If the date is not equal, the first assignment date, the serial number starting from 1 Select @sValue = CONVERT (bigint, CONVERT (varchar (6), GETDATE (),) + ' 000001 ') Upd   
  Ate 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 Serialno Select CONVERT (v Archar (6), GETDATE (), 12) + ' 000001 '