不进入Catch块

Question

我有一个带有事务的SQL存储过程。实际上，我编写了三个阶段的SP，每个阶段在不同的表格中执行某些操作。问题是，当SP到达第三阶段时，其中一列是varchar（20），但我插入了一个具有30个字符的字符串，尽管SP成功完成，但我期望它进入Catch块并进行回滚。第三阶段没有提交，并且没有将行添加到表中，但仍然感觉像“事务”没有起作用，前两个阶段已提交。

以下是我的SQL SP：

USE [dbfoo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[io_sp_admin]
@id BIGINT = 0, @firstName VARCHAR(20),@lastName VARCHAR(20) ,@email VARCHAR(50), @birthDate DATETIME
        
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION [TranAddEmp]
OPEN SYMMETRIC KEY io_key DECRYPTION BY CERTIFICATE foo

--stage1
DECLARE @identity BIGINT = 0 

INSERT INTO [dbo].[t1]
           ([id],
            [deleted],
            [user_name],
     VALUES
           (EncryptByKey(KEY_GUID('io_key'), CONVERT(VARBINARY(100),CAST(@id AS VARCHAR(10)))),
           0,
           'IoAdmin'

SELECT @identity = @@identity 

--satge2
INSERT INTO [dbo].[t2]
           ([id]
           ,[deleted]
           ,[user_name]
     VALUES
           (@identity,
           EncryptByKey(KEY_GUID('io_key'), CONVERT(VARBINARY(100),CAST(@ms_zehut AS VARCHAR(10)))),
           0,
           'IoAdmin'

--stage 3
INSERT INTO t3(
id,
lastName,
firstName,
birtdate,
email)

SELECT
 @identity,
 @lastName , 
 @firstName , 
@birthDate,
 @email 

CLOSE ALL SYMMETRIC KEYS
           
SELECT CAST(1 as BIT) as 'Status', 'Succeeded' as 'ReturnMessage'   
COMMIT TRANSACTION [TranAddEmp]     
END TRY

BEGIN CATCH
 
SELECT CAST(0 as BIT) as 'Status', 'ADMIN - Add employee failed' as 'ReturnMessage'       
ROLLBACK TRANSACTION [TranAddEmp]     
END CATCH
END

- JumpIntoTheWater

---

附注：当触发器存在时，@@Identity可以做一些有趣的事情。Scope_Identity()是一个更好的选择。当涉及多个值时，output子句是一个很棒的东西。 - HABO

十倍的兄弟..我会去看看的。 - JumpIntoTheWater

Answer 1

你可以清楚地看到，如果你传递的字符串长度超过了允许的长度，你将会得到以下错误并且事务将被回滚：

Msg 8152，级别16，状态30，第18行：字符串或二进制数据会被截断。

这意味着输入的字符串长度必须小于或等于规定的长度。否则，数据将无法插入表中，并将导致整个事务失败。

"原始答案"的意思是最初的回答。

DROP TABLE IF EXISTS [dbo].[DataSource];

CREATE TABLE [dbo].[DataSource]
(
    [value] VARCHAR(8)
);

BEGIN TRY

    BEGIN TRANSACTION

    INSERT INTO [dbo].[DataSource] ([value])
    VALUES ('123');

    INSERT INTO [dbo].[DataSource] ([value])
    VALUES ('very large string');

    COMMIT TRANSACTION

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    BEGIN;
        ROLLBACK TRANSACTION;
    END;

    THROW;

END CATCH

SELECT *
FROM [dbo].[DataSource];

最初的回答：你的代码没有问题。但是你通过变量传递字符串，这样值会自动截断以适合变量长度。

所以，请检查以下内容：

DROP TABLE IF EXISTS [dbo].[DataSource];

CREATE TABLE [dbo].[DataSource]
(
    [value] VARCHAR(8)
);

DECLARE @values VARCHAR(8) = 'very large string';

SELECT @values;

BEGIN TRY

    BEGIN TRANSACTION

    INSERT INTO [dbo].[DataSource] ([value])
    VALUES ('123');

    INSERT INTO [dbo].[DataSource] ([value])
    VALUES (@values);

    COMMIT TRANSACTION

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    BEGIN;
        ROLLBACK TRANSACTION;
    END;

    THROW;

END CATCH

SELECT *
FROM [dbo].[DataSource];

因此，在调用存储过程之前，您可以检查输入参数是否有效，或增加输入参数的值并依赖于引擎。无论如何，我更喜欢在使用数据之前对其进行验证 - 对我来说似乎更清晰（如果不允许用户输入更长的名称，为什么要允许呢？）。"最初的回答"

- gotqn