Return Id of created/ updated data from stored procedure

Question

I have stored procedure that create/update data in database. I need to return Id of this data

Here is stored procedure

    CREATE PROCEDURE [ExecutiveChange].[CreateUpdateFeedback] @RecognitionLogId INT,
                                                          @RecognitionStatus VARCHAR(255),
                                                          @FeedbackStatus VARCHAR(255),
                                                          @FeedbackComment VARCHAR(255),
                                                          @FeedbackBy VARCHAR(255),
                                                          @ClarifiedChangeType VARCHAR(255),
                                                          @ClarifiedCompanyName VARCHAR(255),
                                                          @FeedbackApprovedBy VARCHAR(255),
                                                          @FeedbackApprovedDateTime DATETIME
AS

SELECT *
FROM [ExecutiveChange].[RecognitionFeedback] s
WHERE s.RecognitionLogId = @RecognitionLogId;
    IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO [ExecutiveChange].[RecognitionFeedback](RecognitionLogId,
                                                                RecognitionStatus,
                                                                FeedbackStatus,
                                                                FeedbackComment,
                                                                FeedbackBy,
                                                                FeedbackDateTime,
                                                                ClarifiedChangeType,
                                                                ClarifiedCompanyName,
                                                                FeedbackApprovedBy,
                                                                FeedbackApprovedDateTime)
            VALUES (@RecognitionLogId, @RecognitionStatus, @FeedbackStatus, @FeedbackComment, @FeedbackBy, GETDATE(),
                    @ClarifiedChangeType, @ClarifiedCompanyName, @FeedbackApprovedBy, @FeedbackApprovedDateTime);

        END
    ELSE
        BEGIN
            UPDATE [ExecutiveChange].[RecognitionFeedback]
            SET RecognitionLogId         = @RecognitionLogId,
                RecognitionStatus        = @RecognitionStatus,
                FeedbackStatus           = @FeedbackStatus,
                FeedbackComment          = @FeedbackComment,
                FeedbackBy               = @FeedbackBy,
                FeedbackDateTime         = GETDATE(),
                ClarifiedChangeType      = @ClarifiedChangeType,
                ClarifiedCompanyName     = @ClarifiedCompanyName,
                FeedbackApprovedBy       = @FeedbackApprovedBy,
                FeedbackApprovedDateTime = @FeedbackApprovedDateTime

            WHERE RecognitionLogId = @RecognitionLogId
        END
go

How I can return identity form create/update?

You can refer to this https://stackoverflow.com/questions/3142444/stored-procedure-return-identity-as-output-parameter-or-scalar — Zorev Gnoz, Oct 08 '21 at 06:59
This is also a good example https://www.aspsnippets.com/Articles/Return-Identity-value-from-Stored-Procedure-in-SQL-Server.aspx — Zorev Gnoz, Oct 08 '21 at 07:00
Does this answer your question? [SQL Server - Return value after INSERT](https://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert) — Farshid Shekari, Oct 08 '21 at 07:06

Shreyas Prakash · Answer 1 · 2021-10-08T08:12:58.563

You can use @@identity to return the identity of the inserted row. For update, you already know the ID

    CREATE PROCEDURE [ExecutiveChange].[CreateUpdateFeedback] @RecognitionLogId INT,
                                                          @RecognitionStatus VARCHAR(255),
                                                          @FeedbackStatus VARCHAR(255),
                                                          @FeedbackComment VARCHAR(255),
                                                          @FeedbackBy VARCHAR(255),
                                                          @ClarifiedChangeType VARCHAR(255),
                                                          @ClarifiedCompanyName VARCHAR(255),
                                                          @FeedbackApprovedBy VARCHAR(255),
                                                          @FeedbackApprovedDateTime DATETIME
AS

SELECT *
FROM [ExecutiveChange].[RecognitionFeedback] s
WHERE s.RecognitionLogId = @RecognitionLogId;
    IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO [ExecutiveChange].[RecognitionFeedback](RecognitionLogId,
                                                                RecognitionStatus,
                                                                FeedbackStatus,
                                                                FeedbackComment,
                                                                FeedbackBy,
                                                                FeedbackDateTime,
                                                                ClarifiedChangeType,
                                                                ClarifiedCompanyName,
                                                                FeedbackApprovedBy,
                                                                FeedbackApprovedDateTime)
            VALUES (@RecognitionLogId, @RecognitionStatus, @FeedbackStatus, @FeedbackComment, @FeedbackBy, GETDATE(),
                    @ClarifiedChangeType, @ClarifiedCompanyName, @FeedbackApprovedBy, @FeedbackApprovedDateTime);
            SELECT @@IDENTITY AS 'Identity'; -- you can add here after the insert is complete
        END
    ELSE
        BEGIN
            UPDATE [ExecutiveChange].[RecognitionFeedback]
            SET RecognitionLogId         = @RecognitionLogId,
                RecognitionStatus        = @RecognitionStatus,
                FeedbackStatus           = @FeedbackStatus,
                FeedbackComment          = @FeedbackComment,
                FeedbackBy               = @FeedbackBy,
                FeedbackDateTime         = GETDATE(),
                ClarifiedChangeType      = @ClarifiedChangeType,
                ClarifiedCompanyName     = @ClarifiedCompanyName,
                FeedbackApprovedBy       = @FeedbackApprovedBy,
                FeedbackApprovedDateTime = @FeedbackApprovedDateTime
            OUTPUT INSERTED.PrimaryKeyID -- this is your primary key column that you have in your table. Please replace PrimaryKeyID with that
            WHERE RecognitionLogId = @RecognitionLogId
        END
go

so at create I need to add `SELECT @@IDENTITY AS 'Identity';` after `VALUES (@RecognitionLogId, @RecognitionStatus, @FeedbackStatus, @FeedbackComment, @FeedbackBy, GETDATE(), @ClarifiedChangeType, @ClarifiedCompanyName, @FeedbackApprovedBy, @FeedbackApprovedDateTime);` ? — Eugene Sukh, Oct 08 '21 at 07:03
I just modified my answer to show where you can add the select — Shreyas Prakash, Oct 08 '21 at 07:05
But I f I return from update, it will return me id for updated row? yes? — Eugene Sukh, Oct 08 '21 at 08:07
I have modified the answer handling both scenarios. Please see if this works for you. — Shreyas Prakash, Oct 08 '21 at 08:16
Avoid `@@identity` it is not reliable, always use `SCOPE_IDENTITY()` — Charlieface, Oct 08 '21 at 11:33

Farshid Shekari · Answer 2 · 2021-10-08T12:24:41.090

0

One way is to use @@identity like this

SELECT @@IDENTITY

in the section, you want to return. but this way in some situations has problems, you can read it here.

and another one is treated like this:

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

also, you can do it in this way:

SELECT SCOPE_IDENTITY()

edited Oct 08 '21 at 12:24

answered Oct 08 '21 at 07:05

Farshid Shekari

2,391
4
27
47

Avoid `@@identity` it is not reliable, always use `SCOPE_IDENTITY()` – Charlieface Oct 08 '21 at 11:33

Return Id of created/ updated data from stored procedure

2 Answers2