iFinity Blogs 

Detecting an Empty Guid in Sql Server in T-Sql Syntax

by Bruce Chapman on Wednesday, February 17, 2010 1: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.

Blogs Parent Separator Crafty Code
Author
Bruce Chapman

The craft of writing code. The outcomes from being crafty with code. Crafty Code is tales from the coding bench.

1 comment(s) so far...

Anonymous 2/21/2012

For simplicity you could change:<br />SET @emptyGuid = CAST(CAST(0 as BINARY) as UNIQUEIDENTIFIER) <br />SET @emptyGuid = CAST(0x0 AS UNIQUEIDENTIFIER)<br /><br />

Bruce Chapman
Hi, I'm Bruce Chapman, and this is my blog. You'll find lots of information here - my thoughts about business and the internet, technical information, things I'm working on and the odd strange post or two.
Connect with Bruce Chapman on Google+

Share this page
Get more!
Subscribe to the Mailing List
Email Address:
First Name:
Last Name:
You will be sent a confirmation upon subscription

Follow me on Twitter
Stack Exchange
profile for Bruce Chapman at Stack Overflow, Q&A for professional and enthusiast programmers
Klout Profile