Sql-server – Cancel update if no columns changed

sql server

How could SP be modified the way it will execute update only in case any updated value is different?

Assume we have a table

CREATE TABLE students
(
    StudentId INT PRIMARY KEY,
    Name nvarchar(255) NULL    
 )
|----|------|
| Id | Name |
|----|------|
| 1  | Jane |
|----|------|
| 2  | John |
|----|------|
| 3  | NULL |
|----|------|

and the single student update stored procedure

[dbo].[student_update] (@StudentId int, @Name nvarchar(255)) AS
update [dbo].[students]
set [Name] = @Name
where [StudentId] = @StudentId

So again, how this could SP be modified the way it will execute update only in case @Name does not equal to [Name]?

Please take into account that [Name] could be NULL

DECLARE @areFieldsDifferent = CASE WHEN (
([NAME] is null and  @Name is not null) OR
([NAME] is not null and  @Name is null) OR
([NAME] <> @Name)
)
then 1 ELSE 0 END;

But how could be compared @Name with [Name] from Select * From Students where StudentId = @StudentId?
And how to use it to not call update statement?

Best Answer

Don't think about "not calling the update statement." If you have to check the existing value in the table, you have to read the row, whether you're going to update it or not. Then, if you have to update it, you have to read the row again first. How this could possibly be more efficient than just trying to update the row is beyond me.

You can shorthand all that is null/is not null syntax if you can pick a string that can't possibly be a valid name:

UPDATE dbo.students
  SET [Name] = @Name
  WHERE StudentId = @StudentId
  AND ( COALESCE(Name, N'한') <> COALESCE(@Name, N'한') );
  -----------------------^ obscure character that can't be a valid name

Otherwise:

UPDATE dbo.students
  SET [Name] = @Name
  WHERE StudentId = @StudentId
  AND 
  (
        Name <> @Name
        OR ( Name IS NOT NULL AND @Name IS NULL )
        OR ( Name IS NULL AND @Name IS NOT NULL )
  );

If it really is a hard requirement to not fire the UDPATE if it will affect 0 rows, then you need to take a less efficient path and locate the row twice when the update is going to affect a row.

BEGIN TRANSACTION;

IF EXISTS 
(
  SELECT 1 FROM dbo.students WITH (UPDLOCK, HOLDLOCK)
  WHERE StudentId = @StudentId 
    AND 
    (
      Name <> @Name
      OR ( Name IS NOT NULL AND @Name IS NULL )
      OR ( Name IS NULL AND @Name IS NOT NULL )
    )
)
BEGIN
  UPDATE dbo.students 
  SET Name = @Name 
  WHERE StudentId = @StudentId 
    AND 
    (
      Name <> @Name
      OR ( Name IS NOT NULL AND @Name IS NULL )
      OR ( Name IS NULL AND @Name IS NOT NULL )
    );
END

COMMIT TRANSACTION;