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 ***