SQL Server - How to check if a value that is already exist tried to insert to a unique column ?

daowdos 271 Reputation points
2021-08-24T16:09:03.583+00:00

Hi,
I want to send the C# ASP.NET an error if unique key that already exist was tried to insert to the Users table.
EmailAddress is a UNIQUE column.
Am I doing it right or are there other ways to do it ?
Should I return a number for each error and I will catch that number in the C# ASP.NET, for each number I will have a different message to send to the client side ( in SQL - DECLARE @error INT )?

ALTER PROC [dbo].[SignUp]
  @emailAddress NVARCHAR(320),  
 @passwordHash VARCHAR(32),
   AS
   DECLARE @error VARCHAR(20)
   BEGIN TRY
   DECLARE @salt UNIQUEIDENTIFIER=NEWID()
   INSERT INTO Users ( EmailAddress, PasswordHash,Salt)
   VALUES (@emailAddress, HASHBYTES('SHA2_256', @passwordHash+CAST(@salt AS NVARCHAR(36))), @salt )
END TRY
BEGIN CATCH
     SET @error  = "Email address is already exist" 
     SELECT @error
END CATCH
   SELECT EmailAddress  FROM Users
END

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,895 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,826 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2021-08-24T21:23:29.42+00:00

    If you are to do it that way, you need to check the error number, so that you know that the error is a key violation. Now you are assuming that the error is a key violation, but it could be something else.

    However, I would write it so that I check in advance if the address already exists:

    IF NOT EXISTS (SELECT * FROM Users WHERE EmailAddress = @emailAddress)
    BEGIN
        INSERT...
    END
    ELSE
    BEGIN 
       RAISERROR('Email address "%s" is already in use.', 16, 1, @emailAddress)
    END
    

    This raises an exception in the client which is probably desirable.

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Pet Loki 1 Reputation point
    2021-08-24T16:11:57.4+00:00

    You can try inserting it. If it fails then it means it has already been used.
    You can also query first to see if the value is already there.
    You can also store the values you have generated and keep track of its successful usage so you won't use them again