How do I identify a blank uniqueidentifier in SQL Server 2005?

Question

I'm getting a uniqueidentifier into a Stored Procedure that looks like this

00000000-0000-0000-0000-000000000000.

This seems like a simple thing, but how can identify that this is a blank uniqueidentifier?

If I get a value like this DDB72E0C-FC43-4C34-A924-741445153021 I want to do X

If I get a value like this 00000000-0000-0000-0000-000000000000 I do Y

Is there a more elegant way then counting up the zeros?

Thanks in advance

score 35 · Accepted Answer · answered Dec 18 '09 at 22:06

35

compare to

cast(cast(0 as binary) as uniqueidentifier)

?

answered Dec 18 '09 at 22:06

davek

even shorter: `CAST(0x0 AS UNIQUEIDENTIFIER)`, shorterer: `CAST(0x AS UNIQUEIDENTIFIER)` – janv8000 Jun 28 '23 at 09:20

score 4 · Answer 2 · answered Dec 18 '09 at 22:11

4

Just create an EmptyGuid variable and compare against that:

DECLARE @EmptyGuid UniqueIdentifier
SET @EmptyGuid = '00000000-0000-0000-0000-000000000000'

answered Dec 18 '09 at 22:11

Eric Petroelje

score 3 · Answer 3 · answered Dec 18 '09 at 22:12

3

IF (@TheGuid = '00000000-0000-0000-0000-000000000000')
    SELECT 'Do Y'
ELSE
    SELECT 'Do X'

answered Dec 18 '09 at 22:12

LukeH

score 2 · Answer 4 · answered Nov 13 '15 at 14:38

Best solution is to use a constant for the empty GUID

DECLARE @EmptyGuid UNIQUEIDENTIFIER
SET @EmptyGuid = '00000000-0000-0000-0000-000000000000'

OR

DECLARE @EmptyGuid UNIQUEIDENTIFIER
SET @EmptyGuid = 0x0

and you just compare them

IF @parameter = @EmptyGuid
    DO Y
ELSE
    DO X

Note: you don't need to use casts and converts

score 1 · Answer 5 · answered Nov 11 '14 at 14:25

1

This also works.

DECLARE @EmptyGuid UNIQUEIDENTIFIER = CONVERT(UNIQUEIDENTIFIER, 0x0);  
SELECT @EmptyGuid

answered Nov 11 '14 at 14:25

BrianB

The question was on SQL-Server 2005 yours will work from 2008 up. – bummi Nov 11 '14 at 14:35

5 Answers5