HI WELCOME TO SIRIS

How to check GUID is null or empty in SQL Server

Leave a Comment

we will discuss how to check if a GUID is null or empty


How to check if a GUID is NULL : Checking if a GUID is null is straight forward in SQL Server. Just use IS NULL keywords as shown below.

Declare @MyGuid Uniqueidentifier

If(@MyGuid IS NULL)
Begin
     Print 'Guid is null'
End
Else
Begin
     Print 'Guid is not null'
End

In the above example, since @MyGuid is just declared and not initialised, it prints the message "Guid is null"

Now let's say, if a GUID variable is NULL, then we want to initialise that GUID variable with a new GUID value. If it's not NULL, then we want to retain it's value. One way to do this is by using an IF condition as shown below.

Declare @MyGuid UniqueIdentifier

If(@MyGuid IS NULL)
Begin
     Set @MyGuid = NEWID()
End

Select @MyGuid

We can achieve exactly the same thing by using ISNULL() function. The advantage of using ISNULL() function is that, it reduces the amount of code we have to write.

Declare @MyGuid UniqueIdentifier
Select ISNULL(@MyGuid, NewID())

How to check if a GUID is EMPTY : Before understanding how to check if a GUID is empty, let's understand what is an empty GUID. An empty GUID is a GUID with all ZEROS as shown below.
00000000-0000-0000-0000-000000000000

How to create this empty GUID. Do we have to type all the ZERO's and Hyphens. The answe is NO. We do not have to type them manually. Instead use one of the following SELECT query's to create an empty GUID. I prefer to use the second SELECT statement as it has only one CAST

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

To check if a GUID is an empty GUID, you have 2 options
Option 1: You can compare it to an Empty GUID value as shown below

Declare @MyGuid UniqueIdentifier
Set @MyGuid = '00000000-0000-0000-0000-000000000000'

If(@MyGuid = '00000000-0000-0000-0000-000000000000')
Begin
     Print 'Guid is Empty'
End
Else
Begin
     Print 'Guid is not Empty'
End

Option 2: You can also compare it to a return value of the CAST method

Declare @MyGuid UniqueIdentifier
Set @MyGuid = '00000000-0000-0000-0000-000000000000'

If(@MyGuid = Cast(0x0 as Uniqueidentifier))
Begin
     Print 'Guid is Empty'
End
Else
Begin
     Print 'Guid is not Empty'
End

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.