带事务的存储过程

CREATE PROCEDURE [dbo].[Proc_SaveIDCardDetectInfo]
     @IDInfoXml NVARCHAR(MAX),
     @detectXml NVARCHAR(MAX),
     @UserId int,
     @DetectResult bit,
     @Success bit output,
     @Message NVARCHAR(50) output
     
AS
BEGIN

    BEGIN TRANSACTION;
    BEGIN TRY
        Set @Message =''
        --- 保存身份证信息, 上传图片时创建记录,此处只保存信息
        SET NOCOUNT ON;
        DECLARE @hdoc INT
        EXEC sp_xml_preparedocument @hdoc OUTPUT, @IDInfoXml
        
        Set @Success =0
        Declare @IDCardNum VARCHAR(20)
        
        SELECT @IDCardNum=fxml.IDCardNum
        FROM openxml(@hDoc,'/IndentityCardInfoEntity',2)
         with(
                      UserId     int
                     ,IDCardNum VARCHAR(20)) as fxml
        IF EXISTS(SELECT 1 FROM  dbo.RealNameCertResult r INNER JOIN  dbo.IndentityCardInfo ic ON ic.UserId=r.UserId
               WHERE     ic.IDCardNum=@IDCardNum    and r.Result=1)
           BEGIN
               SET @Message ='此身份证号已通过实名认证,不能重复认证' 
               COMMIT TRANSACTION;
                Return
           END
          
                     
                     
        IF NOT EXISTS (SELECT 1 FROM dbo.IndentityCardInfo WHERE UserId=@UserId)
        BEGIN
            INSERT INTO dbo.IndentityCardInfo
             ([UserId])     Values (@UserId)
        END
        
        UPDATE IndentityCardInfo
                SET  --[UserId] =
                IDCardNum=fxml.IDCardNum
               ,[Name]=fxml.Name
               ,Gender=fxml.Gender
               ,Race=fxml.Race
               ,[BirthDay]=fxml.[BirthDay]
               ,[Address]=fxml.[Address]
               ,ValidDate=fxml.ValidDate
               ,IssuedBy=fxml.IssuedBy
               , UpdateTime=GETDATE()
    
                FROM openxml(@hDoc,'/IndentityCardInfoEntity',2)
                     with(
                      UserId     int
                     ,IDCardNum VARCHAR(20)
                     ,Name     NVARCHAR(30)
                     ,Gender   NVARCHAR(10) 
                     ,Race  NVARCHAR(30)
                     ,[BirthDay]  datetime
                     ,[Address]  NVARCHAR(100)
                     ,ValidDate  VARCHAR(20)
                     ,IssuedBy  NVARCHAR(100)
                     --,[IdentityCardPic1]  NVARCHAR(150)
                     --,[IdentityCardPic2] NVARCHAR(150)
                    
                     ) as fxml
                    WHERE IndentityCardInfo.UserId=@UserId
    
        SET NOCOUNT OFF;
        --- 保存检测结果
        EXEC sp_xml_preparedocument @hdoc OUTPUT, @detectXml
        DELETE dbo.IDCardLegalityResult WHERE UserId=@UserId
        INSERT INTO IDCardLegalityResult
           ([UserId]
           ,[Side]
           ,[IDPhoto]
           ,[TemporaryIDPhoto]
           ,[Photocopy]
           ,[Screen]
           ,[Edited])
        SELECT @UserId,  *
                FROM openxml(@hDoc,'/ArrayOfIDCardLegalityResultEntity/IDCardLegalityResultEntity',2)
                     WITH(
                      [Side]     INT
                     ,[IDPhoto] DECIMAL(18,3)
                     ,[TemporaryIDPhoto]    DECIMAL(18,3)
                     ,[Photocopy]   DECIMAL(18,3)
                     ,[Screen]  DECIMAL(18,3)
                     ,[Edited]  DECIMAL(18,3))
                     
        EXEC SP_XML_REMOVEDOCUMENT @hDoc               
        if NOT EXISTS( Select 1 FROM  dbo.IDCardLegalityResult WHERE UserId=@UserId)
        BEGIN
             SET @Message ='合法性概率值参数错误'
             ROLLBACK TRANSACTION;
        END
        ELSE BEGIN
         Set @Success =1
        END
         --- 维护 实名认证结果数据
        IF NOT EXISTS (SELECT 1 FROM dbo.RealNameCertResult WHERE UserId=@UserId)
        BEGIN
            INSERT INTO [RealNameCertResult]
             (
                [UserId]
               ,[IDCardResult]
               ,[IsCompleted]
                )
           VALUES
               (@UserId
               ,@DetectResult
               ,0
               )
        END
        ELSE BEGIN
            UPDATE [RealNameCertResult]
                 SET IDCardResult=@DetectResult
                 WHERE UserId=@UserId
        END

        
     END TRY
     BEGIN CATCH
        
        
         SELECT ERROR_NUMBER () AS ErrorNumber,
                ERROR_SEVERITY () AS ErrorSeverity,
                ERROR_STATE () AS ErrorState,
                ERROR_PROCEDURE () AS ErrorProcedure,
                ERROR_LINE () AS ErrorLine,
                ERROR_MESSAGE () AS ErrorMessage;
         SET @Message =ERROR_MESSAGE()
         Set @Success =0
         IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
      END CATCH;
      COMTran:
        IF @@TRANCOUNT > 0
        BEGIN
         COMMIT TRANSACTION;
       END
END
GO