Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Development Wizard

My name is Peter Skoglund. I have twenty+ years experience in IT and I have work with SQL Server since version SQL Server 7.0. I work as a development DBA and spend all my days with development and database administration. My quote is "The first step to improvement is to have the gut to question".

Don’t Pass Null


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.

Comments

Leave a comment on the original post [sqldevelopmentwizard.blogspot.com, opens in a new window]

Loading comments...