Return Id of created/ updated data from stored procedure-CodePudding

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?

CodePudding user response：

One way is to use @@identity like this

SELECT @@IDENTITY

in the section, you want to return.

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()

CodePudding user response：

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