代码之家 › 专栏 › 技术社区 › JumpIntoTheWater

不进入拦网

transactions error-handling tsql sql-server sql

JumpIntoTheWater · 技术社区 · 6 年前

我有一个SQL存储过程 Transaction . 我通常用三个阶段来写SP,每个阶段在不同的表中做一些事情。问题是,当SP到达第3阶段时,其中一列是 varchar(20) ,但在sp成功完成后,我插入了一个包含30个字符和event的字符串,同时我将其展开以进入catch块并使 ROLLBACK . 第三阶段没有提交,也没有向表中添加任何行,但仍然感觉像 Transcation 没有成功,前两个阶段都完成了。

这是我的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

1 回复 | 直到 6 年前

gotqn user3521065 6 年前

您可以清楚地看到,如果传递的字符串长度大于所允许的长度,则会出现以下错误,并执行事务回滚:

消息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];

因此,您可以在调用存储过程之前检查输入参数是否有效,或者增加输入参数的值并依赖于引擎。不管怎样,我更喜欢在使用前验证数据——这对我来说似乎更清楚了(如果不允许,为什么我们允许用户输入长度更大的名称?).