When working with SQL Server, it’s important to understand how NULL values are handled, especially when using spatial functions. In this blog post, we will explore two different ways to validate NULL values and discuss their use cases.

Using the IsNULL Function

The IsNULL function is a simple and straightforward way to validate if an object is NULL or not. When the object is not NULL, the function returns the value 0. However, if the object is NULL, the function returns NULL.

Let’s take a look at an example:

DECLARE @p GEOMETRY = 'Polygon((2 2, 3 3, 4 4, 5 5, 6 6, 2 2))'
SELECT @p.ISNULL ObjIsNull

In this example, the IsNULL function will return the value 0 because the object is not NULL.

DECLARE @p GEOMETRY = NULL
SELECT @p.ISNULL ObjIsNull

In this second example, the IsNULL function will return NULL because the object is NULL.

Using the IsValidDetailed Function

The IsValidDetailed function is another way to validate if an object is NULL or not. However, it also checks if the object is valid or not. If the object is valid, the function returns the value “24400: Valid”. If the object is not valid, it returns an error message with the error number. In the case of a NULL object, the function returns NULL.

Let’s see an example:

DECLARE @p GEOMETRY = 'Polygon((2 2, 3 3, 4 4, 5 5, 6 6, 2 2))'
SELECT @p.IsValidDetailed() IsValid

In this example, the IsValidDetailed function will return “24400: Valid” because the object is valid.

DECLARE @p GEOMETRY = NULL
SELECT @p.IsValidDetailed() IsValid

In this second example, the IsValidDetailed function will return NULL because the object is NULL.

Choosing the Right Function

Now that we have explored both the IsNULL and IsValidDetailed functions, you may wonder which one to use. The choice depends on your specific use case.

The IsNULL function is preferred when you simply need to determine if an object is NULL or not. It returns a bit value of 0 when the object is NULL, making it easy to compare and check for NULL values.

On the other hand, the IsValidDetailed function is useful when you not only need to validate NULL values but also check if the object is valid or not. However, keep in mind that the return value of IsValidDetailed is nvarchar(max), which may not always be easy to compare.

Ultimately, both functions can be used interchangeably, but understanding their differences and use cases will help you choose the one that best fits your business needs.

Thank you for reading this blog post. We hope it has provided you with a better understanding of NULL handling in SQL Server.