Generating the SQL equivalent of Guid.Empty
I've been refactoring one of our projects today, and one of the areas I've been working on has been reducing 5 or 6 similar stored procedures into 1 more generic version. In doing this, I've had to add a couple of nullable parameters, one of which is a GUID.
If it is specified, then I need to set an AssignedToUser column to be the GUID, and I also need to record the date of the assignment. I can do this all within the UPDATE statement, but I needed to only affect the assignment date when the GUID is specified. Enter the ISNULL function, but what do I set my default value to when it is null? I really wanted to create a known GUID without hardcoding a fixed GUID - a SQL equivalent of Guid.Empty. A quick bit of googling and I came up with
SELECT CAST(CAST(0 as BINARY) AS UNIQUEIDENTIFIER)
which evaluates to
00000000-0000-0000-0000-000000000000
Job done, and recorded here so I can find it next time I need it
