事务

CREATE PROCEDURE TEMP_UpdateWorkRemark
@IsOwn int,
@ReportID int,
@ReportType int,
@RemarkScore float,
@Remark text,
@Reviewer nvarchar(50)
AS
BEGIN
    IF(@IsOwn=1)
        BEGIN TRANSACTION
            DECLARE @errorSum int --错误计数
            SET @errorSum=0
            UPDATE PTS_WorkRemark SET RemarkScore=@RemarkScore,Remark=@Remark;
            SET @errorSum=@errorSum+@@ERROR;
            INSERT INTO PTS_WorkRemark(ReportID,ReportType,RemarkType,Remark,Reviewer,RemarkScore,RemarkTime)
            VALUES(@ReportID,@ReportType,@IsOwn,@Remark,@Reviewer,@RemarkScore,GETDATE());
            IF(@errorSum<>0)
                BEGIN
                    PRINT '有错误,回滚!'
                    ROLLBACK TRANSACTION; 
                    SELECT 0; 
                END 
            ELSE
                BEGIN
                    PRINT '提交成功!'
                    COMMIT TRANSACTION;
                    SELECT 1;
                END
        END
    ELSE
        BEGIN
            INSERT INTO PTS_WorkRemark(ReportID,ReportType,RemarkType,Remark,Reviewer,RemarkScore,RemarkTime)
            VALUES(@ReportID,@ReportType,@IsOwn,@Remark,@Reviewer,@RemarkScore,GETDATE());
            IF(@@ERROR>0)
                SELECT 0;
            ELSE 
                SELECT 1;
        END
END

 

posted @ 2014-03-05 17:25  kedarui  阅读(147)  评论(0编辑  收藏  举报