实用的银行转账存储过程和流水号生成存储过程

银行转账存储过程

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;
  begin
  select @totaldeposit=total from account where accountnum=@outaccount;
  if @totaldeposit is null
  begin
  rollback;
  print'转出账户不存在或账户中没有存款'
  return;
  end
  if @totaldeposit<@amount
  begin
  rollback;
  print'余额不足，不能操作'
  return;
  end
  update account set total=total-@amount where accountnum=@outaccount;
  update account set total=total+@amount where accountnum=@inaccount;
  print'转账成功！'
  commit;
  end;

流水号生成存储过程

if exists(select 1 from sysobjects where id=object_id('getserialno') and xtype='p')
drop proc getserialno
go
create procedure [dbo].[getserialno]  
(  
  @scode varchar(50)  
)  
as 
begin 
 declare @svalue varchar(16),@dtoday  datetime,@sqz varchar(50) --这个代表前缀 
  begin tran   
  begin try  
   -- 锁定该条记录，好多人用lock去锁，起始这里只要执行一句update就可以了 
  --在同一个事物中，执行了update语句之后就会启动锁 
  update serialno set svalue=svalue where scode=@scode  
  select @svalue = svalue from serialno where scode=@scode  
  select @sqz = sqz from serialno where scode=@scode  
   -- 因子表中没有记录，插入初始值  
   if @svalue is null  
   begin 
    select @svalue = convert(bigint, convert(varchar(6), getdate(), 12) + '000001')  
    update serialno set svalue=@svalue where scode=@scode  
   end else  
   begin        --因子表中没有记录  
    select @dtoday = substring(@svalue,1,6)  
    --如果日期相等，则加1  
    if @dtoday = convert(varchar(6), getdate(), 12)  
     select @svalue = convert(varchar(16), (convert(bigint, @svalue) + 1))  
    else       --如果日期不相等，则先赋值日期，流水号从1开始  
     select @svalue = convert(bigint, convert(varchar(6), getdate(), 12) +'000001')  
   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 serialno
select convert(varchar(6), getdate(), 12)+'000001'