SQL 增加, 删除父子级带事务的存储过程

if (object_id('proc_DeleteFile', 'P') is not null)
    drop proc proc_DeleteFile
 go
create  PROCEDURE proc_DeleteFile
(
    @id  nvarchar(40) , --删除的文件ID
 @pid nvarchar(40) ,--项目Id
 @comment  nvarchar(3800) --想历史记录表插入数据
)   
AS
begin
BEGIN TRANSACTION--开始事务

DECLARE @errorSun INT --定义错误计数器
SET @errorSun=0 --没错为0

 DELETE FROM LPProjectDiscuss   WHERE    ElementID=CONVERT(uniqueidentifier, @id) ;--事务操作SQL语句删除讨论信息
SET @errorSun=@errorSun+@@ERROR --累计是否有错

DELETE FROM LPProjectFile   WHERE    LPProjectfileID=CONVERT(uniqueidentifier, @id); --事务操作SQL语句删除文件信息
SET @errorSun=@errorSun+@@ERROR --累计是否有错
insert into LPProjectHistory (HistoryID ,ID, [NewName], ProjectID, NewComment, UpdateTime)values
(
NEWID(),CONVERT(uniqueidentifier, @id), @comment, CONVERT(uniqueidentifier ,@pid),@comment,getdate()
)
SET @errorSun=@errorSun+@@ERROR --累计是否有错

IF @errorSun<>0
BEGIN
PRINT '有错误，回滚'
ROLLBACK TRANSACTION--事务回滚语句
END
ELSE
BEGIN
PRINT '成功，提交'
COMMIT TRANSACTION--事务提交语句
END
end

 

//--------------------------------------------------增加事务----------------------------------------------------------------------------------------------------------

 

 

if (object_id('proc_SaveDiscuss', 'P') is not null)
    drop proc proc_SaveDiscuss
 go

create  PROCEDURE proc_SaveDiscuss
(
 @LPProjectDiscussID nvarchar(36),--要插入的guid
 @DiscussName  nvarchar(100),    --讨论名称
 @DiscussContent nvarchar(2000),
 @ElementID nvarchar(36),
 @UserID nvarchar(36),
 @Status int,
 @CreateTime datetime
)   
AS
begin
BEGIN TRANSACTION--开始事务

DECLARE @errorSun INT --定义错误计数器
SET @errorSun=0 --没错为0
DECLARE @Read nvarchar(15)
set @Read ='增加了讨论'

INSERT INTO LPProjectDiscuss
( LPProjectDiscussID,
DiscussName,
DiscussContent,
ElementID,
UserID,
 [Status],
 CreateTime )
VALUES
(
CONVERT(uniqueidentifier,
 @LPProjectDiscussID),
 @DiscussName,@DiscussContent,
 CONVERT(uniqueidentifier,@ElementID),
 CONVERT(uniqueidentifier ,@UserID),
 @Status,
 @CreateTime
)
SET @errorSun=@errorSun+@@ERROR --累计是否有错

insert into LPProjectHistory
(
HistoryID ,
ID,
[NewName],
ProjectID,
NewComment,
UpdateTime,
Mark,
UserID,
CreateTime,
[Type]
 
 )
 values
(
NEWID(),
CONVERT(uniqueidentifier, @LPProjectDiscussID),
@DiscussName,
 CONVERT(uniqueidentifier,@ElementID),
 @Read+ @DiscussContent,
 ( SELECT dateadd(ms,0,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))),
 CONVERT(int, 0),
  CONVERT(uniqueidentifier ,@UserID),
 (select getdate()),
CONVERT(int, 0)

)
SET @errorSun=@errorSun+@@ERROR --累计是否有错

IF @errorSun<>0
BEGIN
PRINT '有错误，回滚'
ROLLBACK TRANSACTION--事务回滚语句
END
ELSE
BEGIN
PRINT '成功，提交'
COMMIT TRANSACTION--事务提交语句
END
end

 

 

exec proc_SaveDiscuss '193446A7-62B4-A418-DB38-02BB2800F9C2', '今天回家看病','打针','AF0DB0BE-C0EA-5F54-AA2B-79EBBE4C3080','FFBD2905-28E3-0347-FDC4-F94993D1C326',0, '2013-08-09 17:54:39.233'

-------------------------------------------------------------------------------修改的存储过程-------------------------------------------------------------------------