SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 stored procedure update only changed value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

carstentao
Starting Member

Thailand
6 Posts

Posted - 05/14/2013 :  13:11:22  Show Profile  Reply with Quote
Hi,
I got the following problem:
An existing Stored Procedure is used to update DB-Records from a .asp-Application on an "Edit"-Page. On Save-Click, all DB-Fields are updated.

I need to alter this Stored Procedure, so that a particular Field [RoomNo] only gets updated, when the Value in it actually changed.

I tried with ISNULL and COALESCE in the SET-Statement, but it doesn't seem to work at all.

Do I need to use Checksums or additional IF UPDATE ??? I'm a bit lost, so any help would be highly appreciated ...

Here's some Snippet:

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


any idea how to only update the value of RoomNo, if it actually changed when the StoredProcedure is started from the Application ???

Thanks in advance for any input

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 05/14/2013 :  15:11:54  Show Profile  Reply with Quote
I'm not sure why it would matter if the RoomNo gets updated with the same value. 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.
Go to Top of Page

carstentao
Starting Member

Thailand
6 Posts

Posted - 05/14/2013 :  15:58:15  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

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

Thanks for the reply - there's a Trigger on that Table that fires on update of that particular field, that's why I don't want to update it everytime the Table is updated on any of the other fields.

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.



Based on what I posted originally, any idea why the ISNULL / COALESCE don't do the job ... or:
Could you give me an example of how to implement what you just suggested in T-SQL ?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 05/14/2013 :  16:25:25  Show Profile  Reply with Quote
I can only guess why your code doesn't work.. Some issue with the way the calling environment is handling/passing the checksum. Or who knows.

Here is a sample:
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
Go to Top of Page

carstentao
Starting Member

Thailand
6 Posts

Posted - 05/14/2013 :  17:01:25  Show Profile  Reply with Quote
Thanks for your quick reply ...

moved your suggestion right into the Declare Part of the SProc ...

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

and got the outcome that I can now only update records IF the RoomNo actually changes :)
Should the Set Part possibly go into the Set Part of the Procedure as I initially posted? That would eliminate the additional Where as well ... any help is appreciated :)
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1645 Posts

Posted - 05/14/2013 :  17:38:54  Show Profile  Reply with Quote
I think you are trying to do this
UPDATE [dbo].[Customer]
SET 
    [Resort] = @p_Resort,
    [RoomNo] = @p_RoomNo,
    [DepartDate] = @p_DepartDate,
    [Paid_Accomm] = @p_Paid_Accomm
WHERE [CU_ID] = @pk_CU_ID
   AND ([RoomNo] = @p_RoomNo or RoomNo is NULL)


=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)
Go to Top of Page

carstentao
Starting Member

Thailand
6 Posts

Posted - 05/15/2013 :  07:21:58  Show Profile  Reply with Quote
Hi all,
Thanks for the replies ... I tried most options to alter the SProc but either ran into not getting the desired Field Value updated at all (even when the Value changed) or updating all the times. I decided to look more into the problem from the other side and change the Trigger, and in the end, the solution was very simple:

Seeing that the
IF UPDATE (RoomNo)

in the Trigger doesn't really compare the Value of the Field but merely if there's an Update called, I simply had to add the comparison via INSERTED and DELETED, so I added the join with DELETED, set my Where Clause on it and it works :)

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


Thought I post it here for anyone interested :) BTW, is there a way to mark these Threads as answered or so ???
Thanks again everyone for your help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/16/2013 :  00:21:15  Show Profile  Reply with Quote
Yep. UPDATE() just check if column was part of an update operation and doesnt actually care if value was changed or not. For that what you did is the right approach ie comparing INSERTED vs DELETED table values. Also if column is NULLable make sure you handle those conditions as well in check to cover cases where NULL was updated with a value or value was updated back to NULL. <> operator will not work with NULL as intended as NULL is not stored as a value under default conditions in SQL Server. So use COALESCE() or ISNULL() to convert NULLs to invalied value and then do the comparison.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000