適用的銀行轉賬存儲進程和流水號生成存儲進程

適用的銀行轉賬存儲進程和流水號生成存儲進程。本站提示廣大學習愛好者：（適用的銀行轉賬存儲進程和流水號生成存儲進程）文章只能為提供參考，不一定能成為您想要的結果。以下是適用的銀行轉賬存儲進程和流水號生成存儲進程正文

銀行轉賬存儲進程

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'