日期:2014-05-18  浏览次数:20354 次

我写的这个存储过程有问题吗?高手进来指点一下啊,100分
一个在申请取卡号密码的过程.

CREATE   PROCEDURE   getcard
@cardtype   varchar(50),--卡类型
@applydate   datetime,--申请时间
@phsnum   varchar(50),--小灵通号码
@cardnum   varchar(50)   output,--卡号
@cardpwd   varchar(50)   output,--密码
@cardcharge   float   output,--金额
@usefullife   datetime   output,--有效期
@isempty   int   output--是否有卡
as
declare   @cid   int--卡id
BEGIN   TRANSACTION
select   @cid=cid,@cardnum=cardnum,@cardpwd=cardpwd,@cardcharge=cardcharge,@usefullife=CONVERT(char,usefullife,23)   from   cardinfo   where   cardtype=@cardtype   and   isuse=0
if(isnumeric(@cid)> 0)
begin
set   @isempty   =   2
update   cardinfo   set   isuse=1   where   cid=@cid
insert   into   sentlog(cid,phsnum,applydate,senddate)   values(@cid,@phsnum,@applydate,getdate());
end
else
begin
set   @isempty   =   1
end
COMMIT
GO


------解决方案--------------------
没做错误处理。
------解决方案--------------------
BEGIN TRANSACTION的前边增加一句:

set xact_abort on

用于确保事务的完整性。
------解决方案--------------------
CREATE PROCEDURE getcard
@cardtype varchar(50),--卡类型
@applydate datetime,--申请时间
@phsnum varchar(50),--小灵通号码
@cardnum varchar(50) output,--卡号
@cardpwd varchar(50) output,--密码
@cardcharge float output,--金额
@usefullife datetime output,--有效期
@isempty int output--是否有卡
as
declare @cid int--卡id
BEGIN TRANSACTION
select @cid=cid,@cardnum=cardnum,@cardpwd=cardpwd,@cardcharge=cardcharge,@usefullife=CONVERT(char,usefullife,23)
from cardinfo
where cardtype=@cardtype and isuse=0
IF (@rowcount_var = 0) or(@error_var!=0)
begin
Rollback tran
set @isempty = 0
RETURN
end
if(isnumeric(@cid)> 0)
begin
set @isempty = 2
update cardinfo set isuse=1 where cid=@cid
IF (@rowcount_var = 0) or(@error_var!=0)
begin
Rollback tran
set @isempty = 0
RETURN
end
insert into sentlog(cid,phsnum,applydate,senddate) values(@cid,@phsnum,@applydate,getdate());
IF (@rowcount_var = 0) or(@error_var!=0)
begin
Rollback tran
set @isempty = 0
RETURN
end
end
else
begin
set @isempty = 1
end
COMMIT TRANSACTION
GO