转账 存储过程
alter proc usp_trancMoney
@from char(4),
@to char(4),
@money money ,
@isok int output
as
begin
declare @have money =-1 ,@iscunzai int =0
select @have=balance from bank where cId=@from
--看看要存入的账号是否存在
if not exists(
select cid from bank where cId=@to)
begin
set @isok=4 --要转入的账号不存在
return
end
if @have<0 --要转出的账号是否存在
begin
set @isok=3 ---wu zhang hao
end
else
begin
if @have-@money<10
begin
print @have
set @isok=2 --wu ge bu zu
end
else
begin
print '转账kaishi '
--开始转账
begin transaction
update bank set balance=balance-@money where cId=@from
set @isok=@isok+@@error
update bank set balance=balance+@money where cId=@to
set @isok=@isok+@@error
if @isok>0
begin
set @isok=1--error
rollback
end
else
begin
set @isok=0 --ok
commit tran
end
end
end
end
---------测试
declare @out int
declare @str nvarchar(33)
exec usp_trancMoney @from='0005',@to='0003' ,@money=10 ,@isok=@out output
select @str=
case @out
when 0 then '成功了'
when 1 then '失败了'
when 2 then '余额不足'
when 3 then '不存在的账号(转出)'
when 4 then '要转入的账号不存在'
else '未知错误'
end
print @str
ado.net调用存储过程
在sqlhelper中
1 //1.执行增（insert）、删(delete)、改(update)的方法
2 //ExecuteNonQuery()
3 public static int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] pms)
4 {
5 using (SqlConnection con = new SqlConnection(conStr))
6 {
7 using (SqlCommand cmd = new SqlCommand(sql, con))
8 {
9 //cmd.CommandType = CommandType.Text;//这个是默认的，当使用sql语句的时候传入这个
10 //cmd.CommandType = CommandType.StoredProcedure;//存储过程
11 cmd.CommandType = cmdType;
12 if (pms != null)
13 {
14 cmd.Parameters.AddRange(pms);
15 }
16 con.Open();
17 return cmd.ExecuteNonQuery();
18 }
19 }
20 }
调用
SqlParameter[] pms = new SqlParameter[]{
new SqlParameter("@from",SqlDbType.Char,4){ Value=from},
new SqlParameter("@to",SqlDbType.Char,4){ Value=to},
new SqlParameter("@money",SqlDbType.Money){ Value=money},
new SqlParameter("@isok",SqlDbType.Int){ Direction=ParameterDirection.Output}
};
//调用存储过程实现转账
SqlHelper.ExecuteNonQuery("usp_trancMoney", CommandType.StoredProcedure, pms);
int result = Convert.ToInt32(pms[3].Value);
