Working with GUID variables is a highly useful way of developing software. If you don’t know, GUID stands for ‘Globally Unique Identifier’. It’s a way of generating a value that is guaranteed to be different from any other value, generated anywhere else in the world. It’s a neat idea. I once read there are more possible values for a GUID than there are stars in the universe. I don’t know how accurate that is, but at least it makes me feel better everytime I ‘waste’ a Guid by creating it and then not using it again.
Moving right along, the reason for the post is that I came across an issue today where I was generating records in .NET code, and then saving those to a Sql Server database. So far so normal. In this case, however, I am using a GUID type to link a foreign key record to a table. The problem I had was that the .NET equivalent of an empty guid (detectable by the statement Guid.Empty) is a string of zeroes, like this:
In this case, I’d rather make use of the NULL value in Sql Server. It’s much more correct in my worldview to use a NULL where no value exists, rather than an all-zeroes value. This is important if you’re using referential integrity for an optional many-to-one or one-to-one relationship.
I combed through the T-SQL documentation, looking for an elusive ‘Guid.Empty’ equivalent, and found nothing. After searching around I found some very weird and wonderful ways of replicating this functionality, so I decided to ignore all that and write my own.
This function returns a NULL value if the Guid variable is empty. You could adapt this for other purposes, or just use the relevant pieces of code within it. Either way, I hope it helps someone combing the web looking for a solution.
Heres the code to create the function:
-- Author: Bruce Chapman
-- Description: Returns null if an empty guid
CREATE FUNCTION fnNullIfEmptyGuid
declare @result uniqueidentifier
declare @emptyGuid uniqueidentifier
set @emptyGuid = cast(cast(0 as binary) as uniqueidentifier)
select @result = case when @guidValue = @emptyGuid then null else @guidValue end
And here’s how you would use it (assuming in a stored procedure):
Insert into A_Table (KeyId, SomeData, GuidValue)
values ( @keyId, @someData, dbo.fnNullIfEmptyGuid(@guid))
The principals are just the same for any other requirement where you need to detect an empty guid in Sql Server.