Update data with If exists in SQL Server Stored Procedure

---

There is a table structure here:

CREATE TABLE [dbo].[FEEDBACK_LOG](
    [Idx] [int] IDENTITY(1,1) NOT NULL,
    [FBIdx] [bigint] NOT NULL,
    [EventStatus] [int] NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [Length] [int] NOT NULL,
 CONSTRAINT [PK_FEEDBACK_LOG] PRIMARY KEY CLUSTERED 
(
    [Idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now we want to update the "EndTime" and "Length" which the Idx is specified ID number, but actually we don’t know ID number, we only know FBIdx, which is another ID will help to find main ID number.

There are other conditions so that we can locate the main ID number: we only update the record which Length = 0, and FBIdx = the FBIdx which we know, if multiple rows found, we always select the last one (Idx will be the max).

So we create a stored procedure like following:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================================
-- Author:    ...
-- Create date:    ...
-- Description:    Update FeedBack End Time
-- ===============================================================
CREATE PROCEDURE [dbo].[UpdateFeedBackEndTime]
    @vFBIdx int,
    @vEndtime datetime,
    @vLength int
AS
BEGIN
    SET NOCOUNT ON
    
                -- retrieve idx number first
    DECLARE @idx int
    SET @idx = (select max([Idx]) 
                from [dbo].[FEEDBACK_LOG]
                where ([FBIdx] = @vFBIdx) and ([Length] = 0))
    
    IF not exists (SELECT 1 FROM [dbo].[FEEDBACK_LOG] WHERE [Idx] = @idx)
    BEGIN
       --print 0
       return 0
    END   
    ELSE
    BEGIN
        UPDATE [dbo].[FEEDBACK_LOG]
           SET [EndTime] = @vEndtime, [Length] = @vLength
         WHERE [Idx] = @idx 
         --print @@ROWCOUNT
         return @@ROWCOUNT
        END
 END