Update followed by insert in a stored procedure

Question

I'm not sure that's the correct way making an update followed by insert in a stored procedure.

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

Answer 1

A simple change to your current code can give you what you're looking for. Instead of messing around with @@Identity, which is almost never the right thing to do, you compute the hash of the @Id value once, store it in a local variable, and use it for both the insert statement and the where clause of the update statement - That is, assuming the HashId column is unique.

That being said, I'm not sure why you need the rowId column as well as the incId column - unless one of them is designed to change it's value through an update statement in the lifetime of the row - you are simply keeping redundant data.

Here's an improved version of your stored procedure:

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