Question: My insert procedure is working fine the way i want. But update is not working with scope identity.

```
SET ANSI_NULLS ON

ALTER PROCEDURE [dbo].[spr_unitCreation]
(
    @Unit_Name VARCHAR(50) = NULL,
    @Unit_Abbreviation VARCHAR(50) = NULL,
    @Unit_type VARCHAR(50) = NULL,
    @Decimal_Places VARCHAR(50) = NULL,
    @Description VARCHAR(50) = NULL,
    @Super_Unit VARCHAR(50) = NULL,
    @Per_Unit VARCHAR(50) = NULL,
    @unit_Id INT OUTPUT,
    @abc VARCHAR(50) = NULL
)
AS
BEGIN
    IF @abc = 'update' BEGIN

        SET NOCOUNT ON;

        SELECT @unit_Id AS SCOPE_IDENTITY

        UPDATE tbl_UnitCreation
        SET Unit_Name = @Unit_Name,
            Unit_Abbreviation = @Unit_Abbreviation,
            Unit_type = @Unit_type,
            Decimal_Places = @Decimal_Places,
            Description = @Description,
            Super_Unit = @Super_Unit,
            Per_Unit = @Per_Unit
        WHERE unit_Id = @unit_Id
    END

END

SELECT * FROM tbl_UnitCreation

```

Answer: 
SCOPE\_IDENTITY returns the last identity value inserted into an identity column. You are not inserting a row.

To update a row all you need is to pass a value to @unit\_Id when executing [spr\_unitCreation]. Also remove the line "SELECT @unit\_Id AS SCOPE\_IDENTITY" from your code.

Based on the comments, you need to find the correct id by searching on relevant details. So you can get the id like this:

```
SELECT @unit_Id = unit_Id
FROM tbl_UnitCreation
WHERE Unit_Name=@Unit_Name -- NB: Ensure this column contains your relevant details

```
Another commonly used option is to use the OUTPUT clause of the UPDATE statement, inserting all the updated/"inserted" primary keys and Unit\_name into a tablevariable.

<https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017>