Select id of user for specific email, or max(id)+1 if email doesn’t exist

I want to get the id of a user for a given email, or if the email doesn’t exist, I want it to return MAX(id)+1.

So for the example below, I want:

• For email ‘[e-mail]’, I want it to return 1
• For email ‘[e-mail]’, I want it to return 9

I made an attempt but I couldn’t make it work the way I want.

DECLARE @USERS TABLE (id int, name varchar(50), email varchar(50));

INSERT INTO @USERS(id, name, email) VALUES
(1, 'john', '[e-mail]'),
(2, 'nick', '[e-mail]'),
(3, 'alex', '[e-mail]'),
(4, 'liam', '[e-mail]'),
(5, 'noah', '[e-mail]'),
(6, 'oliver', '[e-mail]'),
(7, 'james', '[e-mail]'),
(8, 'rob', '[e-mail]');

SELECT CASE WHEN (ISNULL(id, 0) = 0) THEN id ELSE (MAX(id) + 1)
FROM @USERS
WHERE email = '[e-mail]';

Answer

Two sub-queries inside a coalesce should do the job.

DECLARE @TestEmail VARCHAR(50) = '[e-mail]';

SELECT 
    COALESCE((SELECT TOP 1 id FROM @USERS WHERE email = @TestEmail), (SELECT MAX(id) + 1 FROM @USERS), 1);

This will only work in a simple scenario when you don’t call this concurrently, and you make use of the returned ID before you would ever call this again. Otherwise you would need to perform some locking to ensure it works as expected.