Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
carstentao
Starting Member
6 Posts |
Posted - 2013-05-14 : 13:11:22
|
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)ASDECLARE @l_newValue nvarchar(4000), @return_status int, @l_rowcount intBEGIN-- 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) ENDENDGO 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
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-05-14 : 15:11:54
|
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 PK2. Compare the selected value with the parameter value.3. If different, then update. Otherwise do not. |
|
|
carstentao
Starting Member
6 Posts |
Posted - 2013-05-14 : 15:58:15
|
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 PK2. 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 ? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-05-14 : 16:25:25
|
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 INTSET @RoomNo =( SELECT RoomNo FROM dbo.Customer WHERE CU_ID = @pk_CU_ID)IF @RoomNo IS NULL OR @p_RoomNo <> @RoomNoBEGIN -- UPDATEEND |
|
|
carstentao
Starting Member
6 Posts |
Posted - 2013-05-14 : 17:01:25
|
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 <> @RoomNoBEGIN 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 :) |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-05-14 : 17:38:54
|
I think you are trying to do this[CODE]UPDATE [dbo].[Customer]SET [Resort] = @p_Resort, [RoomNo] = @p_RoomNo, [DepartDate] = @p_DepartDate, [Paid_Accomm] = @p_Paid_AccommWHERE [CU_ID] = @pk_CU_ID AND ([RoomNo] = @p_RoomNo or RoomNo is NULL)[/CODE]=================================================I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain) |
|
|
carstentao
Starting Member
6 Posts |
Posted - 2013-05-15 : 07:21:58
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-16 : 00:21:15
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|