SQL Server - Stop user from making secondary account without a main account-CodePudding

I've been trying to get my code to work but it isn't functioning as intended, basically @NAME is what the user inputs when trying to create a new user, the @Tmpname is the name without any numbers, as there are original accounts (John Smith) and secondary accounts (John Smith 2, John Smith 3, John Smith 4 and so on).

I want @Name to IS NULL if there is no original user and they're treating to create a secondary account. The script will only fire if @Name has a number in it, so original accounts being made will not be effected by this search

Example of what is intended,

User attempts to create 'John Smith 2', there is no 'John Smith' so it NULLs @Name (and errors the script)

Currently it will null out @Name even if there is an existing user, I don't want this,

Example of what it does currently User attempts to create 'John Smith 2', there is a 'John Smith', it nulls @Name and errors the script

SELECT @NAME = --If user is attempting to make a secondary account without an original account
CASE
    WHEN @TmpName = name
THEN @Name
ELSE NULL
END
FROM t_employee
WHERE @NAME LIKE '%[0-9]%'

This is my first time on this platform, so please let me know if you require any more information, the error definitely lies within this piece of code and not elsewhere in my script as commenting it out resolves any errors.

CodePudding user response：

If I've understood what you're trying to do, something like this should work:

If @Name Like '%[0-9]%' And Not Exists
(
    SELECT 1
    FROM t_employee
    WHERE name = @TmpName
)
BEGIN
    SET @Name = NULL;
END;