Update data with If exists in SQL Server Stored Procedure
2011-06-22
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