Alternatives · Basic Logics · Data Management · Real Scenario Logic's

UPDATE Optional and Mandatory columns with values using T-SQL – Issue with Solution

Introduction

In this Blog post, we are going to see What are the issues while updating both Optional and Mandatory columns and What is the solution and How we need to Handle/UPDATE both Optional and Mandatory Column values in SQL Server using T-SQL.

Scenario

Let us assume, we have the TABLE named Student with COLUMNS like Student_Name and Favorite_Subject. As per business requirement, Column_1 – Student_Name should have a valid name(Say for example, it cannot be empty). Column_2 – Favorite_Subject can be optional. Both of the above fields should be editable. While Editing anyone of the 2 fields, other field should remains same.

Sample SQL Script

IF OBJECT_ID('TEMPDB..#Student') IS NOT NULL
DROP TABLE #Student;

CREATE TABLE #Student
(
Student_key INT IDENTITY(1,1) NOT NULL,
Student_Name VARCHAR(100) NOT NULL,
Favorite_Subject VARCHAR(50)
);

INSERT INTO #Student(Student_Name,Favorite_Subject)
VALUES
('Arul','Tamil'),
('Bala','English');

SELECT * FROM #Student;

Issues –

1. Mandatory Column is Updated as Empty!

SQL Script for Issue Scenario

--Mandatory Column is Updated as Empty! (Student_Name is updated as empty while User trying to change Favorite_Subject)
--Scenario 1 => User trying to change Favorite_Subject as 'English' for Student_key=1 and API gives below values to SP.
DECLARE @Student_key INT,@Student_Name VARCHAR(100),@Favorite_Subject VARCHAR(50);
SELECT  @Student_key=1,
		@Student_Name='', --Assume API Layer gives Empty value instead of NULL to sp
		@Favorite_Subject='English';

UPDATE #Student
SET Student_Name=ISNULL(@Student_Name,Student_Name), 
	Favorite_Subject=ISNULL(@Favorite_Subject,Favorite_Subject)
WHERE Student_key=@Student_key;

SELECT * FROM #Student;

SQL Script – Solution!

--Mandatory Column is Not Updated as Empty! (Student_Name remains same while User trying to change Favorite_Subject)
--Scenario 1 => User trying to change Favorite_Subject as 'English' for Student_key=1 and API gives below values to SP.
DECLARE @Student_key INT,@Student_Name VARCHAR(100),@Favorite_Subject VARCHAR(50);
SELECT  @Student_key=1,
		@Student_Name='', --Assume API Layer gives Empty value instead of NULL to sp
		@Favorite_Subject='English';

UPDATE #Student
SET Student_Name=COALESCE(NULLIF(@Student_Name,''),Student_Name), 
	Favorite_Subject=COALESCE(NULLIF(@Favorite_Subject,''),Favorite_Subject)
WHERE Student_key=@Student_key;

SELECT * FROM #Student;

2. Optional Column is Not Updating Correctly!

SQL Script for Issue Scenario

--Optional Column is Not Updating Correctly! (Favorite_Subject is not updated as Empty while User trying to Remove Favorite_Subject)
--Scenario 2 => User trying to Remove Favorite_Subject for Student_key=2 and API gives below values to SP.
DECLARE @Student_key INT,@Student_Name VARCHAR(100),@Favorite_Subject VARCHAR(50);
SELECT  @Student_key=2,
		@Student_Name='', --Assume API Layer gives Empty value instead of NULL to sp
		@Favorite_Subject=''; ----Assume API Layer gives Empty value to remove his 'Favorite_Subject' to sp

UPDATE #Student
SET Student_Name=COALESCE(NULLIF(@Student_Name,''),Student_Name), 
	Favorite_Subject=COALESCE(NULLIF(@Favorite_Subject,''),Favorite_Subject)
WHERE Student_key=@Student_key;

SELECT * FROM #Student;

SQL Script – Solution!

--Optional Column is Updating Correctly! (Favorite_Subject is updated as Empty while User trying to Remove Favorite_Subject)
--Scenario 2 => User trying to Remove Favorite_Subject for Student_key=2 and API gives below values to SP.
DECLARE @Student_key INT,@Student_Name VARCHAR(100),@Favorite_Subject VARCHAR(50);
SELECT  @Student_key=2,
		@Student_Name='', --Assume API Layer gives Empty value instead of NULL to sp
		@Favorite_Subject=''; ----Assume API Layer gives Empty value to remove his 'Favorite_Subject' to sp

UPDATE #Student
SET Student_Name=COALESCE(NULLIF(@Student_Name,''),Student_Name), 
	Favorite_Subject=COALESCE(@Favorite_Subject,Favorite_Subject)
WHERE Student_key=@Student_key;

SELECT * FROM #Student;

Note –

Actually, ISNULL and COALESCE functions having many huge differences. In the above example mentioned, we can use any of the two. For Mandatory Columns, Please plan to handle both NULL and Empty and for optional columns, we can handle NULL . If we need to handle both empty and NULL for optional column too, then we can take any hard-code values as sign and update accordingly.

Conclusion

In the above example, we used NULLIF function along with COALESCE function to handle mandatory columns and COALESCE function alone for optional column. Thus we saw the issues while updating both Optional and Mandatory columns and the solutions to Handle/UPDATE both Optional and Mandatory Column values in SQL Server using T-SQL.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

2 thoughts on “UPDATE Optional and Mandatory columns with values using T-SQL – Issue with Solution

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s