iFinity

Written by:
Wednesday, February 17, 2010 12:56 PM 

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:

00000000-0000-0000-0000-000000000000

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
(
    @guidValue uniqueidentifier
)
RETURNS uniqueidentifier
AS
BEGIN
    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

    RETURN @result

END
GO

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.

Trackback Print

Tags:

Categories:

Location: Blogs Crafty Code

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel