Recently, I needed a way to quickly update some bulk data based on a part number… I started by building 2 stored procedure that each updated different columns, but a few minutes later I realized I was going to need at least 2 more permutations of my update procedure since there were more columns combinations I needed to update. I didn’t want to maintain a bunch of SP for different types of updates, I just wanted a handy update SP that would update a column/field with data when I passed it in or ignored it if left it blank/null.

I came up with a T-SQL Optional Update Parameter solution, which is based on using the “COALESCE” function along with typed null values. Normally, you pass in a bunch of values and it returns the first non null value, but… There is a hidden gem, you can pass in typed null values and it will return null… Knowing this, I created the code below.

-- CREATE DEMO TABLE
CREATE TABLE [dbo].[Product](
	[PartNumber] [nvarchar](20) NULL,
	[Description] [nchar](20) NULL,
	[Comments] [nchar](20) NULL
) ON [PRIMARY];

GO

-- DEMO TABLE DATA
PRINT '';
PRINT 'INSERT SAMPLE DATA';
PRINT '---------------------------------------------------------- '
INSERT INTO [PRODUCT] VALUES (N'PART1', N'PART 1 Description', N'PART 1 Comment');

GO

-- CREATE DEMO PROCEDURE
CREATE PROCEDURE [dbo].[spProduct_Update]
	@PartNumber AS NVARCHAR(20),
	@Description AS NCHAR(20),
	@Comments AS NCHAR(20)
AS
BEGIN
	DECLARE @BEFORE AS NVARCHAR(200);
	DECLARE @AFTER AS NVARCHAR(200);

	SELECT @BEFORE = 'BEFORE: ' + [PartNumber] + '  |  ' + [Description] + '  |  ' + [Comments] FROM [Product] WHERE [PartNumber] = @PartNumber;

	UPDATE [Product]
		SET [Description] = COALESCE(@Description,[Description]),
			[Comments] = COALESCE(@Comments,[Comments])
	WHERE [PartNumber] = @PartNumber;

	SELECT @AFTER = ' AFTER: ' + [PartNumber] + '  |  ' + [Description] + '  |  ' + [Comments] FROM [Product] WHERE [PartNumber] = @PartNumber;

	PRINT @BEFORE;
	PRINT @AFTER;

END

GO

-- Fails : Both values are non-typed null values
PRINT '';
PRINT '';
PRINT 'FAIL = COALESCE(null,null)';  --2 non-typed null value

GO

DECLARE @MyField AS NVARCHAR(50);
SET @MyField = COALESCE(null, null);
PRINT @MyField;

GO

-- Pass : The second value is a typed null value
PRINT '';
PRINT '';
PRINT 'PASS = COALESCE(null,<typed null parameter>)';  --1 non-typed null value, 1 typed null value

GO

DECLARE @MyField AS NVARCHAR(50);
DECLARE @MyTypedParameter AS NVARCHAR(50);
SET @MyField = COALESCE(null, @MyTypedParameter);
PRINT @MyField;

GO

-- Using the COALESCE with a typed parameter to create an optional "column" update.
PRINT '';
PRINT '---------------------------------------------------------- '
PRINT 'NO UPDATES';
EXEC spProduct_Update 'PART1', null, null;

PRINT '';
PRINT '---------------------------------------------------------- '
PRINT 'UPDATE DESCRIPTION ONLY';
EXEC spProduct_Update 'PART1', 'PART 1 *** UPDATE DESCRIPTION ***', null;

PRINT '';
PRINT '---------------------------------------------------------- '
PRINT 'UPDATE COMMENTS ONLY';
EXEC spProduct_Update 'PART1', null, 'PART 1 *** UPDATE COMMENT ***';

PRINT '';
PRINT '---------------------------------------------------------- '
PRINT 'UPDATE DESCRIPTION & COMMENTS';
EXEC spProduct_Update 'PART1', '*** UPDATE BOTH ***', '*** UPDATE BOTH ***';

-- DELETE DEMO PROCEDURE
DROP PROCEDURE [dbo].[spProduct_Update];

GO

-- DELETE DEMO TABLE
DROP TABLE [dbo].[Product];

GO

In a nutshell, we can only use COALESCE with typed nullable values… this means COALESCE(null,null,null) will fail because null is not a defined type, but COALESCE(@nullvalue, @nullvalue, @nullvalue) will work since we had to declare a type of @nullvalue [e.g. DECLARE @nullvalue AS nvarchar(20)]

INSERT SAMPLE DATA
---------------------------------------------------------- 
(1 row(s) affected)
 
FAIL = COALESCE(null,null)
Msg 4127, Level 16, State 1, Line 3
At least one of the arguments to COALESCE must be a typed NULL.
 
 
PASS = COALESCE(null,<typed null parameter>)
 
---------------------------------------------------------- 
NO UPDATES

(1 row(s) affected)
BEFORE: PART1  |  PART 1 Description    |  PART 1 Comment      
 AFTER: PART1  |  PART 1 Description    |  PART 1 Comment      
 
---------------------------------------------------------- 
UPDATE DESCRIPTION ONLY

(1 row(s) affected)
BEFORE: PART1  |  PART 1 Description    |  PART 1 Comment      
 AFTER: PART1  |  PART 1 *** UPDATE DE  |  PART 1 Comment      
 
---------------------------------------------------------- 
UPDATE COMMENTS ONLY

(1 row(s) affected)
BEFORE: PART1  |  PART 1 *** UPDATE DE  |  PART 1 Comment      
 AFTER: PART1  |  PART 1 *** UPDATE DE  |  PART 1 *** UPDATE CO
 
---------------------------------------------------------- 
UPDATE DESCRIPTION & COMMENTS

(1 row(s) affected)
BEFORE: PART1  |  PART 1 *** UPDATE DE  |  PART 1 *** UPDATE CO
 AFTER: PART1  |  *** UPDATE BOTH ***   |  *** UPDATE BOTH ***