sql – 更新,然后在存储过程中插入

我不确定这是更正的方法,然后在存储过程中插入.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[io_sp_admin_add_emp]
    @id BIGINT,
    @lastName VARCHAR(20), 
    @firstName VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
    BEGIN TRANSACTION [TranAddEmp]
       DECLARE @identity BIGINT = 0 

       INSERT INTO empTable(LastName, FirstName, hash_id)
       VALUES (@lastName, @firstName,
               HashBytes('SHA2_256', CAST(@id AS VARBINARY(50))))

       SELECT @identity = @@identity

       UPDATE empTable
       SET rowId = incId  -- both are columns in empTable
       WHERE hash_id = @identity

       COMMIT TRANSACTION [TranAddEmp]   
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION [TranAddEmp]
    END CATCH                  
END
对当前代码的简单更改可以为您提供所需的内容.
你可以计算@Id值的哈希值,将其存储在局部变量中,并将其用于insert语句和where子句,而不是乱用@@ Identity,这几乎不是正确的事情.更新语句 – 也就是说,假设HashId列是唯一的.

话虽这么说,我不确定为什么你需要rowId列和incId列 – 除非其中一个被设计为通过行的生命周期中的更新语句来改变它的值 – 你只是保留冗余数据.

这是存储过程的改进版本:

CRETAE PROCEDURE [dbo].[io_sp_admin_add_emp]
    @id BIGINT,
    @lastName varchar(20), 
    @firstName varchar(20)      
AS

BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION [TranAddEmp]

        -- Compute the hash once, store in a local variable
        DECLARE @HashId varbinary(8000) = HashBytes('SHA2_256', cast(@id as varbinary(50)))

        INSERT INTO empTable(
            LastName,
            FirstName,
            hash_id
        )
        VALUES(
            @lastName,
            @firstName,
            @HashId
        )

         UPDATE empTable
         SET rowId = incId  
         WHERE hash_id = @HashId

        COMMIT TRANSACTION [TranAddEmp]   
    END TRY

    BEGIN CATCH
        -- make sure transaction has started and is not commited
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION [TranAddEmp]
    END CATCH
END
相关文章
相关标签/搜索