NULL == NULL, in SQL Server that is never gonna happen
Learned a little "gotcha" yesterday when I was debugging a stored procedure that used coalesce with both a parameter and the column I was writing a conditional on.
I had something like the following:
CREATE PROCEDURE dbo.getPeopleSP
(@LocationID INT = NULL)
AS
SELECT
dbo.People.ID,
dbo.People.Name,
dbo.People.LocationID
FROM dbo.People
WHERE dbo.People.LocationID = COALESCE(@LocationID, dbo.People.LocationID)
Thinking that since the @LocationID was optional, the COALESCE would take care of the rest if @LocationID was NULL. This is NOT the case. In order do what I had wanted you have to do this:
CREATE PROCEDURE dbo.getPeopleSP
(@LocationID INT = NULL)
AS
SELECT
dbo.People.ID,
dbo.People.Name,
dbo.People.LocationID
FROM dbo.People
WHERE (@LocationID IS NULL OR dbo.People.LocationID = @LocationID)
Not a deal breaker for me, I wish there was an optional parameter in COALESCE to handle it the way you would think it would, NULL being equal to NULL.