First encounter with T-SQL

I am building a little app for my team that collects weekly status emails and publishes it on a web site. Since I've worked on far lower level stuff most of my career (I only wrote the code that actually has UI at Google), it's fun and educating.

Using ASP.NET is quite fun actually - Microsoft REALLY nailed developer experience on this one. The API is logical, concise, well structured, and a pure joy to use. I started with having no clue whatsoever, and had a fully-functioning web app running in around two hours, and this included reading a book.

This story is about T-SQL, however. There came time where I wanted a stored procedure. On my website, there is an adminitrative user who can make changes in a database, and everybody else can do unlimited reading (anybody can see everybody's status mail), but only very limited types of writing (sign up to receive someone else's status updates in email, turn email reminders for her- or himself on and off, and join and depart a project).

So while everything other type of data access was done using LINQ, it seemed convenient to handle mutations by non-admin users via stored procedures. Specifically, I needed one right off the bat: any authenticated user - know by the alias - could sign up for status email for a person whose status was to be collected.

The task seemed quite obvious:

-- =============================================
-- Author:  Sergey Solyanik
-- Create date: 11/14/2008
-- Description: Adds a subscription
-- =============================================
CREATE PROCEDURE [dbo].[AddSubscriptionToPerson]
    @alias nvarchar(15),
    @personId int
AS
BEGIN
    DECLARE @subscriberId int
    
    SET @subscriberId = (SELECT SubscriberId
                         FROM Subscribers
                         WHERE SubscriberAlias = @alias)

    IF @subscriberId = NULL  -- BUGBUG: '=' does not work!
    BEGIN
        INSERT INTO Subscribers (SubscriberAlias) VALUES(@alias)
        SET @subscriberId = @@IDENTITY
    END

    IF NOT EXISTS(SELECT 1
                  FROM SubscriptionsPeople
                  WHERE SubscriberId = @subscriberId AND
                        PersonId = @personId)
    BEGIN
        INSERT INTO SubscriptionsPeople (PersonId, SubscriberId)
        VALUES(@personId, @subscriberId)
    END
END

Right? Wrong! If @subscriberId is NULL, the following statement:

IF @subscriberId = NULL
  SELECT 'IS NULL'
else
  SELECT 'IS NOT NULL'

prints out 'IS NOT NULL'! The correct way of tesing for NULL is not comparing with NULL, but using IS [NOT] NULL keyword:

IF @subscriberId IS NULL
  SELECT 'IS NULL'
else
  SELECT 'IS NOT NULL'

So the version of code that actually works is:

-- =============================================
-- Author:  Sergey Solyanik
-- Create date: 11/14/2008
-- Description: Adds a subscription
-- =============================================
CREATE PROCEDURE [dbo].[AddSubscriptionToPerson]
    @alias nvarchar(15),
    @personId int
AS
BEGIN
    DECLARE @subscriberId int
    
    SET @subscriberId = (SELECT SubscriberId
                         FROM Subscribers
                         WHERE SubscriberAlias = @alias)

    IF @subscriberId IS NULL
    BEGIN
        INSERT INTO Subscribers (SubscriberAlias) VALUES(@alias)
        SET @subscriberId = @@IDENTITY
    END

    IF NOT EXISTS(SELECT 1
                  FROM SubscriptionsPeople
                  WHERE SubscriberId = @subscriberId AND
                        PersonId = @personId)
    BEGIN
        INSERT INTO SubscriptionsPeople (PersonId, SubscriberId)
        VALUES(@personId, @subscriberId)
    END
END

Unfortuately, discovering this was not trivial. You see, IS [NOT] NULL is nowhere near IF in the documentation, and neither it is in the book I was reading. Also, EXISTS works if you put your SQL query that returns NULL in the IF itself, like so:

IF NOT EXISTS(SELECT SubscriberId
              FROM Subscribers
              WHERE SubscriberAlias = @alias)
THEN...

...but not like this:

SET @id = (SELECT SubscriberId
           FROM Subscribers
           WHERE SubscriberAlias = @alias)
IF EXISTS(@id)...

Well, what should we expect from a language with the syntax and semantics right out of 1950s. I haven't worked with anything like this since FORTRAN...