Cancel update if no columns changed

Question

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?

Logically speaking, the column Name should not be nullable. Fix that and part of your issue evaporates. Natural key enforcement is always required - an identity column does not relieve you of that responsibility. — SMor, Mar 11 '20 at 16:21
This answer may add details about what you're looking for https://dba.stackexchange.com/questions/114360 — Alexis Wilke, Mar 15 '22 at 02:22

score 1 · Accepted Answer · answered Mar 11 '20 at 16:12

Put your conditions in the WHERE clause:

update 
    [dbo].[students]
set 
    [Name] = @Name
where 
    [StudentId] = @StudentId
    and 
    -- add your conditions here
    ([Name] <> @Name 
     or ([Name] is null and @Name is not null)
     or ([Name] is not null and @Name is null))

Cancel update if no columns changed

1 Answers1