Last friday my colleague Luca was “fighting” with a problem in managing null values and GUIDs. A Guid is a value type so it is not nullable. In my stored procedures when using parameters for filtering, I use the NULL value as the default value to ignore a filter parameter.
Changing the code of several classes to be able to change a Guid.Empty into a DbNull before passing it to a SELECT command was decisely not the best thing to do, so we searched a way to recreate a Guid.Empty value in SQL.
We were able to create the small snippet of SQL in the following function:
CREATE FUNCTION Guid_Empty()
RETURNS UniqueIdentifier
AS
BEGIN
RETURN cast(cast(0 as binary) as uniqueidentifier)
END
GO
After the creation of the above function and a simple table on my Paperinik Database, I was able to write the following query:
SELECT [ID]
,[Descrizione]
,[Prezzo]
,[Quantità]
,[Umi]
,IsNull(AltGuid, dbo.Guid_EMPTY()) AS Alt
FROM [paperinik].[dbo].[TbRighe]
The query produces the following output:
I can use it also to build a query like the one below:
SELECT [ID]
,[Descrizione]
,[Prezzo]
,[Quantità]
,[Umi]
,IsNull(AltGuid, dbo.Guid_EMPTY()) AS Alt
FROM [paperinik].[dbo].[TbRighe]
WHERE
IsNull(AltGuid, dbo.Guid_EMPTY()) = dbo.Guid_EMPTY()
That allows me to filter only the Guid Empty rows, or I can use the <> to find only the rows with a non Empty Guid. This way of writing the query, is giving me the possibility to use Guid.Empty in my Code as a SQLParameter value without the need to use a Nullable Guid or to implement complicated control expressions in my C# classes.
Technorati Tag:
SQL,
C#,
Guid