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?

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