Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
Contact the author
for this blog
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".
- Posted on 8 June 2012
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. 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]( [Name] [varchar](50) NOT NULL, [ManagerId] [int] NULL) 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) CREATE PROCEDURE [dbo].[ManagerHierarchyByManagerId] @ManagerId INTASBEGIN ; 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=@ManagerIdEND The data looks like this:1 John Smith NULL 12 Ron Lewis 1 23 Will Thomas 2 34 Ron Miller 2 35 Bill Miller 3 4 All works fine when someone passes a numerical argument to the procedure: EXEC [dbo].[ManagerHierarchyByManagerId] 35 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 INTASBEGIN If @ManagerId IS NULL THROW 51000, 'InvalidArgumentException.', 1; 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 ) WHERE ManagerId=@ManagerIdEND 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 INTASBEGIN SELECT @ManagerId=ISNULL(@ManagerId,-1) 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)=@ManagerIdEND 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... 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.
Leave a comment on the original post
[sqldevelopmentwizard.blogspot.com, opens in a new window]