Java自学者论坛

 找回密码
 立即注册

手机号码,快捷登录

恭喜Java自学者论坛(https://www.javazxz.com)已经为数万Java学习者服务超过8年了!积累会员资料超过10000G+
成为本站VIP会员,下载本站10000G+会员资源,会员资料板块,购买链接:点击进入购买VIP会员

JAVA高级面试进阶训练营视频教程

Java架构师系统进阶VIP课程

分布式高可用全栈开发微服务教程Go语言视频零基础入门到精通Java架构师3期(课件+源码)
Java开发全终端实战租房项目视频教程SpringBoot2.X入门到高级使用教程大数据培训第六期全套视频教程深度学习(CNN RNN GAN)算法原理Java亿级流量电商系统视频教程
互联网架构师视频教程年薪50万Spark2.0从入门到精通年薪50万!人工智能学习路线教程年薪50万大数据入门到精通学习路线年薪50万机器学习入门到精通教程
仿小米商城类app和小程序视频教程深度学习数据分析基础到实战最新黑马javaEE2.1就业课程从 0到JVM实战高手教程MySQL入门到精通教程
查看: 450|回复: 0

SQL Server 2008 存储过程+事务+异常捕获+输出参数

[复制链接]
  • TA的每日心情
    奋斗
    2024-11-24 15:47
  • 签到天数: 804 天

    [LV.10]以坛为家III

    2053

    主题

    2111

    帖子

    72万

    积分

    管理员

    Rank: 9Rank: 9Rank: 9

    积分
    726782
    发表于 2021-7-13 14:25:28 | 显示全部楼层 |阅读模式

    带异常输出参数

    如果要在最后再提交事务,那么 TRANSACTION 后面必须加 RETURN,否则脚本会报:COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION 的错

    /*
    
    declare @mesg nvarchar(max)      
    EXEC ZL_AutoCertificate_Build 22922,@mesg OUTPUT
    SELECT @mesg
    
    */
    
    /****** Object:  StoredProcedure [dbo].[ZL_AutoCertificate_Build]    Script Date: 09/28/2018 10:04:08 ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZL_AutoCertificate_Build]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[ZL_AutoCertificate_Build]
    GO
    
    /****** Object:  StoredProcedure [dbo].[ZL_AutoCertificate_Build]    Script Date: 09/28/2018 10:04:08 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE PROCEDURE [dbo].[ZL_AutoCertificate_Build](    
    @ProduceTaskID INT,
    @mesg nvarchar(MAX) OUTPUT        
    )      
    AS    
      
    BEGIN  
        SET @mesg=''
        
        DECLARE @ZL_ConstructionName NVARCHAR(200) 
        DECLARE @ZL_CustomerName NVARCHAR(200) 
        DECLARE @CementGradeID INT 
        DECLARE @BeginTime DATETIME 
        DECLARE @SumCube DECIMAL(18,2)
        
        BEGIN TRANSACTION
        
        SELECT @ZL_ConstructionName=r.ZL_GongCheng,@ZL_CustomerName=r.ZL_WeituoDanWei,
        @CementGradeID=(SELECT TOP 1 CementGradeID FROM Biz_Sell_CementGrade WHERE Name=r.ZL_QiangDu),
        @BeginTime=r.ZL_YuanCaiQianFaRiQi ,@SumCube=r.ZL_FangLiang
        FROM ZL_RenWu AS r
        WHERE r.ProduceTaskID=@ProduceTaskID
        
        BEGIN TRY 
        
        INSERT INTO dbo.Testing_Certificate ([No],ZL_ConstructionName, ZL_CustomerName,
                    ZL_RenWuID, CementGradeID, BeginTime, EndTime, SumCube, UserID,
                    UserName, Createtime)
                    VALUES((SELECT dbo.NO_Testing_CertificateNO()),@ZL_ConstructionName,@ZL_CustomerName,@ProduceTaskID,
                    @CementGradeID,@BeginTime,GETDATE(),@SumCube,52,'Admin',GETDATE())
        DECLARE @CertificateID INT   
        SET @CertificateID=@@IDENTITY;  
        
        DECLARE @BriquetteEntrustItemID INT 
        DECLARE @BriquetteEntrustItemNo NVARCHAR(MAX) 
        DECLARE @ConcreteInspectionTypeID INT
        
        DECLARE cur_cer CURSOR DYNAMIC FOR 
        SELECT BriquetteEntrustItemID,b.[No]+'-'+bi.TestPieceNO,b.ConcreteInspectionTypeID
          FROM Biz_Tech_BriquetteEntrustItem bi           
        INNER JOIN Biz_Tech_BriquetteEntrust AS b ON b.BriquetteEntrustID = bi.BriquetteEntrustID          
        WHERE b.ProduceTaskID=@ProduceTaskID
        OPEN cur_cer 
        FETCH NEXT FROM cur_cer INTO @BriquetteEntrustItemID,@BriquetteEntrustItemNo,@ConcreteInspectionTypeID
        WHILE(@@FETCH_STATUS=0)
        BEGIN
            IF(@ConcreteInspectionTypeID=1)
            BEGIN
                DECLARE @ConcreteInspectionID_TKY INT 
                IF NOT EXISTS(SELECT 1 FROM Testing_ConcreteInspectionItem_TKY WHERE BriquetteEntrustItemID=@BriquetteEntrustItemID)
                BEGIN
                    SET @mesg=@mesg+@BriquetteEntrustItemNo+','
                END
                ELSE
                BEGIN
                    SELECT @ConcreteInspectionID_TKY=ConcreteInspectionID FROM Testing_ConcreteInspectionItem_TKY WHERE BriquetteEntrustItemID=@BriquetteEntrustItemID
                    INSERT INTO dbo.Testing_CertificateOfConcrete (CertificateID,ConcreteInspectionID) VALUES(@CertificateID,@ConcreteInspectionID_TKY)
                END            
            END
    
            IF(@ConcreteInspectionTypeID=2)
            BEGIN
                DECLARE @ConcreteInspectionID_TKS INT 
                IF NOT EXISTS(SELECT 1 FROM Testing_ConcreteInspectionItem_TKS WHERE BriquetteEntrustItemID=@BriquetteEntrustItemID)
                BEGIN
                    SET @mesg=@mesg+@BriquetteEntrustItemNo+','
                END
                ELSE
                BEGIN
                    SELECT @ConcreteInspectionID_TKS=ConcreteInspectionID FROM Testing_ConcreteInspectionItem_TKS WHERE BriquetteEntrustItemID=@BriquetteEntrustItemID
                    INSERT INTO dbo.Testing_CertificateOfConcrete (CertificateID,ConcreteInspectionID) VALUES(@CertificateID,@ConcreteInspectionID_TKS)
                END
            END        
            
            FETCH NEXT FROM cur_cer INTO @BriquetteEntrustItemID,@BriquetteEntrustItemNo,@ConcreteInspectionTypeID
        END
        CLOSE cur_cer 
        DEALLOCATE cur_cer
        
        IF(@mesg<>'')
        BEGIN
            SET @mesg='检验委托没有做实验,编号:'+@mesg
            ROLLBACK TRANSACTION
            RETURN 
        END
        
        END TRY
        BEGIN CATCH
            SET @mesg=ERROR_MESSAGE()
            ROLLBACK TRANSACTION
            RETURN 
        END CATCH
    
        COMMIT TRANSACTION
        
    END   
    
    GO

     

    哎...今天够累的,签到来了1...
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    QQ|手机版|小黑屋|Java自学者论坛 ( 声明:本站文章及资料整理自互联网,用于Java自学者交流学习使用,对资料版权不负任何法律责任,若有侵权请及时联系客服屏蔽删除 )

    GMT+8, 2024-12-23 08:34 , Processed in 0.056705 second(s), 27 queries .

    Powered by Discuz! X3.4

    Copyright © 2001-2021, Tencent Cloud.

    快速回复 返回顶部 返回列表