Sql Server 测试：存储+异常抛出+事务

exec up_testExceptionThrown
SELECT * FROM TestExceptionThrownTB

USE [master]
IF EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME ='TestExceptionThrownDB')
BEGIN
    DROP DATABASE TestExceptionThrownDB
END
GO
CREATE DATABASE TestExceptionThrownDB
GO
USE TestExceptionThrownDB
GO
IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME ='TestExceptionThrownTB')
BEGIN
    DROP TABLE TestExceptionThrownTB
END
CREATE TABLE TestExceptionThrownTB(
    [Id] INT PRIMARY KEY IDENTITY(1,1),
    [Name] VARCHAR(25) 
)
GO
IF OBJECT_ID('up_testExceptionThrown1') IS NOT NULL 
    BEGIN
        DROP PROCEDURE up_testExceptionThrown1
    END
go 
CREATE PROC [dbo].up_testExceptionThrown1
AS
/***********************************************************************************
作者：向以胜
日期：2020-04-05
功能：测试异常抛出
exec up_testExceptionThrown1
***********************************************************************************/
BEGIN
    PRINT 'exec up_testExceptionThrown 1 start';
    IF EXISTS (SELECT * FROM TestExceptionThrownTB WHERE [Name]='test')
    BEGIN
        RAISERROR ('存在',16,1);RETURN;
    END
    ELSE
    BEGIN
        RAISERROR ('不存在',16,1);RETURN;
    END
    PRINT 'exec up_testExceptionThrown 1 end';
END
GO
IF OBJECT_ID('up_testExceptionThrown') IS NOT NULL 
    BEGIN
        DROP PROCEDURE up_testExceptionThrown
    END
go 
CREATE PROC [dbo].up_testExceptionThrown
AS
/***********************************************************************************
作者：向以胜
日期：2020-04-05
功能：测试异常抛出
exec up_testExceptionThrown
SELECT * FROM TestExceptionThrownTB
***********************************************************************************/
BEGIN
    DECLARE @Errmsg NVARCHAR(300)
    BEGIN TRY
        BEGIN TRANSACTION 
        PRINT 'exec up_testExceptionThrown start';
        INSERT INTO TestExceptionThrownTB VALUES('test');
        EXEC up_testExceptionThrown1
        PRINT 'exec up_testExceptionThrown end';
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        SELECT @Errmsg=ERROR_MESSAGE();
        GOTO Error
    END CATCH
    ERROR:
        ROLLBACK TRANSACTION
        PRINT 'ROLLBACK TRANSACTION Errmsg：'+ @Errmsg
END