CREATE PROCEDURE [dbo].[pALPHACustomerUpdate]
    @pk_CU_ID int,
    @p_Resort int,
    @p_RoomNo int,
    @p_DepartDate datetime,
    @p_Paid_Accomm bit,
    @p_prevConValue nvarchar(4000),
    @p_force_update  char(1)
AS
DECLARE
    @l_newValue nvarchar(4000),
    @return_status int,
    @l_rowcount int
BEGIN
-- Check whether the record still exists before doing update
    IF NOT EXISTS (SELECT * FROM [dbo].[Customer] WHERE [CU_ID] = @pk_CU_ID)
        RAISERROR ('Concurrency Error: The record has been deleted by another user. Table [dbo].[Customer]', 16, 1)
    -- If user wants to force update to happen even if 
    -- the record has been modified by a concurrent user,
    -- then we do this.
    IF (@p_force_update = 'Y')
        BEGIN
            -- Update the record with the passed parameters
            UPDATE [dbo].[Customer]
            SET 
            [Resort] = @p_Resort,
            [RoomNo] = @p_RoomNo,RoomNo,
            [DepartDate] = @p_DepartDate,
            [Paid_Accomm] = @p_Paid_Accomm
            WHERE [CU_ID] = @pk_CU_ID
            -- Make sure only one record is affected
            SET @l_rowcount = @@ROWCOUNT
            IF @l_rowcount = 0
                RAISERROR ('The record cannot be updated.', 16, 1)
            IF @l_rowcount > 1
                RAISERROR ('duplicate object instances.', 16, 1)
        END
    ELSE
        BEGIN
            -- Get the checksum value for the record 
            -- and put an update lock on the record to 
            -- ensure transactional integrity.  The lock
            -- will be release when the transaction is 
            -- later committed or rolled back.
            Select @l_newValue = CAST(BINARY_CHECKSUM([CU_ID],[Resort],[RoomNo],[DepartDate],[Paid_Accomm]) AS nvarchar(4000)) 
            FROM [dbo].[Customer] with (rowlock, holdlock)
            WHERE [CU_ID] = @pk_CU_ID
            -- Check concurrency by comparing the checksum values
            IF (@p_prevConValue = @l_newValue)
                SET @return_status = 0     -- pass
            ElSE
                SET @return_status = 1     -- fail
            -- Concurrency check passed.  Go ahead and 
            -- update the record
            IF (@return_status = 0)
                BEGIN

                    UPDATE [dbo].[Customer]
                    SET 
                    [Resort] = @p_Resort,
                    [RoomNo] = @p_RoomNo,
                    [DepartDate] = @p_DepartDate,
                    [Paid_Accomm] = @p_Paid_Accomm

                    WHERE [CU_ID] = @pk_CU_ID

                    SET @l_rowcount = @@ROWCOUNT
                    IF @l_rowcount = 0
                        RAISERROR ('The record cannot be updated.', 16, 1)
                    IF @l_rowcount > 1
                        RAISERROR ('duplicate object instances.', 16, 1)

                END
            ELSE
            -- Concurrency check failed.  Inform the user by raising the error
                RAISERROR ('Concurrency Error: The record has been updated by another user. Table [dbo].[Customer]', 16, 1)

        END
END
GO 

quote:

---

Originally posted by Lamprey

I'm not sure why it would matter if the RoomNo gets updated with the same value.

---

quote:

---

But, maybe something like:
1. Select the RoomNo from the table based on the PK
2. Compare the selected value with the parameter value.
3. If different, then update. Otherwise do not.

---

DECLARE @RoomNo INT

SET @RoomNo =
(
	SELECT RoomNo
	FROM dbo.Customer
	WHERE CU_ID = @pk_CU_ID
)

IF @RoomNo IS NULL OR @p_RoomNo <> @RoomNo
BEGIN
	-- UPDATE
END

DECLARE
    @l_newValue nvarchar(4000),
    @return_status int,
    @l_rowcount int,
    @RoomNo int
    Set @RoomNo = (Select RoomNo from dbo.Customer
    Where CU_ID = @pk_CU_ID)
    IF @RoomNo IS NULL OR @p_RoomNo <> @RoomNo
BEGIN

         IF UPDATE (RoomNo) 

	join Deleted Del 
	ON INS.[CU_ID] = DEL.[CU_ID]
	WHERE INS.[RoomNo] <> DEL.[RoomNo]