# 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?

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;