如何回滚存储过程中的更改 [英] how to rollback changes in stored procedure
问题描述
我有两个表OPDPatient和注册这两个表通过REGNO链接。
现在我想更新两个表的记录。为此,我首先删除了位于两个表中的特定REGNO的所有记录。现在我想如果第一个表的记录被成功删除,那么只有第二个表将用于删除,然后更新操作发生如下:
ALTER proc Sp_Update_OPD_Registration
(
@PID varchar(30),
@Department int,
@Doctor varchar(20),
@ReffBy varchar(50),
@Diagnosis nvarchar(150),
@Category int,
@MLC char(3),
@PoliceStation varchar(100),
@FIRno varchar(100 ),
@Identification varchar(100),
@Injury varchar(100),
@Signature varchar(100),>
@MLC_Charge varchar(150),
@Willing varchar(50),
@Remark varchar(500),
@broughtby varchar(50),
@contactno varchar(50),
@DoctorFee钱,
@AddFee钱,
@Reason varchar(100),
@Discount money
)
as
声明@result tinyint
if(@PID<>''''或@ PID<> null)
BEGIN
从OpdPatient删除RegNo = @ PID - 如果成功则
从注册中删除RegNo = @ PID- - 然后这个删除发生
- 然后这个更新操作
更新OpdPatient设置
部门= @部门,医生= @医生,ReffBy = @ ReffBy,诊断= @诊断,类别= @类别,MLC = @ MLC,PoliceStation = @ PoliceStation,FIRno = @ FIRno,鉴定= @鉴定,损伤= @损伤,签名= @签名,MLC_Charge = @ MLC_Charge,愿意= @Willing,备注= @Remark,带来= @ bringby,contactno = @ contactno,DoctorFee = @ DoctorFee,AddFee = @ AddFee,Reason = @ Reason,Discount = @ Discount其中RegNo = @ PID
END
Hi,
I have two table OPDPatient And Registration these two table tables are linked via REGNO.
Now I want to update records of both table. For this I first deleted all the record of that particular REGNO which lies in both tables.Now i want if record of first table is deleted successfully then only second table will go for delete and then update operation occur like this:
ALTER proc Sp_Update_OPD_Registration
(
@PID varchar(30),
@Department int,
@Doctor varchar(20),
@ReffBy varchar(50),
@Diagnosis nvarchar(150),
@Category int ,
@MLC char(3),
@PoliceStation varchar(100),
@FIRno varchar(100),
@Identification varchar(100),
@Injury varchar(100),
@Signature varchar(100),
@MLC_Charge varchar(150),
@Willing varchar(50),
@Remark varchar(500),
@broughtby varchar(50),
@contactno varchar(50),
@DoctorFee money,
@AddFee money,
@Reason varchar(100),
@Discount money
)
as
Declare @result tinyint
if(@PID <>'''' or @PID<> null)
BEGIN
Delete from OpdPatient where RegNo=@PID--if it is successfull
Delete from Registration where RegNo=@PID--then this delete occur
-- then this update operation
update OpdPatient set
Department=@Department,Doctor=@Doctor,ReffBy=@ReffBy,Diagnosis=@Diagnosis,Category=@Category,MLC=@MLC,PoliceStation=@PoliceStation,FIRno=@FIRno,Identification=@Identification,Injury=@Injury,Signature=@Signature,MLC_Charge=@MLC_Charge,Willing=@Willing,Remark=@Remark,broughtby=@broughtby,contactno=@contactno,DoctorFee=@DoctorFee,AddFee=@AddFee,Reason=@Reason,Discount=@Discount where RegNo=@PID
END
推荐答案
访问链接...
查看使用 Transaction-Rollback 的示例
http://stackoverflow.com/questions/3935900 /如何对提交和回滚事务,在-SQL -server [ ^ ]
快乐编码!
:)
visit link...
see example of using Transaction-Rollback
http://stackoverflow.com/questions/3935900/how-to-commit-and-rollback-transaction-in-sql-server[^]
Happy Coding!
:)
这篇关于如何回滚存储过程中的更改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!