I’ve been rereading an excellent book called “Clean Code” by Robert C. Martin. This book has some practices that each developer and DBA should follow be few does; the key is to help to keep your code clean and maintainable.
Most of the book is written in a Java/C# perspective, but it the practices can be used with ANY code, even T-SQL.
Don’t Pass Null
Returning null from methods is often bad, but passing null into stored procedures is worse especially if you have multiple parameters (Unless you are working with an API which expects you to pass null). You should avoid passing null in your code whenever possible. You can get multiple/not optimal query plans and unexpected results when passing null to a stored procedure.
Let’s look at an example, here is a simple employee manager hierarchy table with data and a stored procedure which returns hierarchy based on the mangers id:
CREATE TABLE [dbo].[ManagerHierarchy]
(
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[ManagerId] [int] NULL
)
GO
INSERT INTO [ManagerHierarchy] values (1,'John Smith',null)
INSERT INTO [ManagerHierarchy] values (2,'Ron Lewis',1)
INSERT INTO [ManagerHierarchy] values (3,'Will Thomas',2)
INSERT INTO [ManagerHierarchy] values (4,'Ron Miller',2)
INSERT INTO [ManagerHierarchy] values (5,'Bill Miller',3)
GO
CREATE PROCEDURE [dbo].[ManagerHierarchyByManagerId]
@ManagerId INT
AS
BEGIN
; WITH ManagerCTE AS (
SELECT *,1 as [Level] FROM [ManagerHierarchy] WHERE Id=1
UNION ALL
SELECT b.*,[Level]+1 as [Level] FROM BossCTE as a
JOIN [ManagerHierarchy] as b on b.[ManagerId]=a.Id
)
SELECT * from ManagerCTE
WHERE ManagerId=@ManagerId
END
GO
The data looks like this:
Id Name ManagerId Level
1 John Smith NULL 1
2 Ron Lewis 1 2
3 Will Thomas 2 3
4 Ron Miller 2 3
5 Bill Miller 3 4
All works fine when someone passes a numerical argument to the procedure:
EXEC [dbo].[ManagerHierarchyByManagerId] 3
Id Name ManagerId Level
5 Bill Miller 3 4
What happens when someone passes null as an argument?
EXEC [dbo].[ManagerHierarchyByManagerId] NULL
We’ll get no rows! Of course, since NULL = NULL is not true! This is why we don’t get John Smith.
How can we fix this, maybe so null is not allowed as a parameter? We could create an exception and throw it:
CREATE PROCEDURE [dbo].[ManagerHierarchyByManagerId]
@ManagerId INT
AS
BEGIN
If @ManagerId IS NULL
THROW 51000, 'InvalidArgumentException.', 1;
; WITH ManagerCTE AS (
SELECT *,1 as [Level] FROM [ManagerHierarchy] WHERE Id=1
UNION ALL
SELECT b.*,[Level]+1 as [Level] FROM BossCTE as a
JOIN [ManagerHierarchy] as b on b.[ManagerId]=a.Id
)
SELECT * from ManagerCTE
WHERE ManagerId=@ManagerId
END
Is this better? It is a little better than unexpected results, but remember, we have to define a handler for InvalidArgumentException. What should the handler do? Is there any good course of action? Is there no way to fix this?
We could return data for null parameter, by assigning a value for null in the procedure:
CREATE PROCEDURE [dbo].[ManagerHierarchyByManagerId]
@ManagerId INT
AS
BEGIN
SELECT @ManagerId=ISNULL(@ManagerId,-1)
; WITH BossCTE AS (
SELECT *,1 as [Level] FROM [ManagerHierarchy] WHERE Id=1
UNION ALL
SELECT b.*,[Level]+1 as [Level] FROM BossCTE as a
JOIN [ManagerHierarchy] as b on b.[ManagerId]=a.Id
)
SELECT * from BossCTE
WHERE ISNULL(ManagerId,-1)=@ManagerId
END
Is this better? Well, we get the row for John Smith, by maybe not the data we want. John Smith, has no manager should he ever be returned? Was the passing of null intentional or by programing mistake? What if there was another record in the table with a ManagerId with null? What if you only wanted the John Smith...
Conclusion
Robert C. Martin conclusion is “In most programming languages there is no real good way to deal with a null that is passed by a caller accidentally. Because this is the case, the rational approach is to forbid passing null by default. When you do, you can code with the knowledge that a null in an argument list is an indication of a problem, and end up with far fewer careless mistakes.”
I agree, avoid passing null and my tip to readers is to use a default value for columns that allows null.
