Check for empty GUID in SQL

Question

How do I check if an argument in a stored procedure is an empty GUID or not?

Solution 1

SELECT CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER)

That should return your empty guid.

... or even shorter, saving one cast:

SELECT CAST(0x0 AS UNIQUEIDENTIFIER)

So to check for that, you would do

IF @GuidParam = CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER)
BEGIN
   --Guid is empty
END

Solution 2

Since the empty guid never changes, the other obvious way is to simply use 00000000-0000-0000-0000-000000000000 rather than calculating it.

If @Param = '00000000-0000-0000-0000-000000000000'
...

Or, if in an procedure, you can set a parameter to act as a constant:

Declare @EmptyGuid uniqueidentifier
Set @EmptyGuid = '00000000-0000-0000-0000-000000000000'

Or you could create a scalar user-defined function which simply returns the above constant value (or recalculates it as in Meiscooldude solution).

Solution 3

DECLARE @EmptyGuid UNIQUEIDENTIFIER = 0x0
DECLARE @NonEmpty UNIQUEIDENTIFIER = NEWID()
IF @EmptyGuid = 0x0 PRINT 'Empty'
IF @NonEmpty = 0x0 PRINT 'Empty' ELSE PRINT 'NonEmpty'

Will print

Empty

NonEmpty

Solution 4

You can make Empty Guid like this:

DECLARE @EmptyGuid UNIQUEIDENTIFIER
SET @EmptyGuid = (SELECT CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER))

-- Single result is 00000000-0000-0000-0000-000000000000
SELECT @EmptyGuid

View more solutions