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 ‘aaa@gmail.com’, I want it to return 1
• For email ‘kkk@gmail.com’, 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', 'aaa@gmail.com'),
(2, 'nick', 'bbb@gmail.com'),
(3, 'alex', 'ccc@gmail.com'),
(4, 'liam', 'ddd@gmail.com'),
(5, 'noah', 'eee@gmail.com'),
(6, 'oliver', 'fff@gmail.com'),
(7, 'james', 'ggg@gmail.com'),
(8, 'rob', 'hhh@gmail.com');

SELECT CASE WHEN (ISNULL(id, 0) = 0) THEN id ELSE (MAX(id) + 1)
FROM @USERS
WHERE email = 'ccc@gmail.com';

>Solution :

The following should satisfy syntax requirements:

select coalesce(max(id), (select coalesce(max(id), 0) + 1 from @users))
from @users
where email = 'ccc1@gmail.com'

That being said, the max id could change between selecting and using it in another query.