sql2005transfer.txt

SQL2005通过存储过程实现金额转账功能

USE [Bank]
GO
/****** 对象:  StoredProcedure [dbo].[procupdatebank]    脚本日期: 11/11/2008 09:23:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[procupdatebankeachother]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[procupdatebankeachother]
go
--定义两个参数帐号@account1/取出金额@changeM/转入@account2
create PROC [dbo].[procupdatebankeachother]
  @account1 int ,@account2 int ,@changeM money	
AS
begin try
   begin tran

--判断该帐号是否存在
IF (@account1 not in(select accounts from dbo.account) and @account2 not in(select accounts from dbo.account)) 	
  BEGIN
    RAISERROR('Find fial of this account!', 14, 1)
    RETURN
  END
--判断帐号@account1/取出金额@changeM是否超出其余额balance
IF (@changeM >(select balance from dbo.account where accounts=@account1) ) 	-- validate
  BEGIN
    RAISERROR('Money changing fail!', 14, 1)
    RETURN
  END


update Bank.dbo.account
 set balance=balance+@changeM

WHERE	accounts=@account2
update Bank.dbo.account
 set balance=balance-@changeM

WHERE	accounts=@account1
--判断经过转账后@account1帐户余额是否小于5.(是否违反check约束)
IF ((select balance from dbo.account where accounts=@account1)<5 ) 	
  BEGIN
    RETURN
  END

commit tran
end try
begin catch
rollback tran
select ERROR_NUMBER() ERRORNUMBER,ERROR_MESSAGE() [MESSAGE]
END CATCH

--执行存储过程，先查看account表中数据（帐户是否存在）
--select * from dbo.account
--exec dbo.procupdatebankeachother @account1=7,@account2=1,@changeM=4999