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

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′