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

Question

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

daowdos 296

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

EchoLiu-MSFT 14,626 Reputation points

2021-08-25T06:25:19.087+00:00

Hi @Elado,

Sorry, your question is not clear enough for me. Please help clarify the meaning of this sentence.

Regards
Echo
Viorel 126.9K Reputation points

2021-08-25T07:04:11.727+00:00

I think that your client ASP code should have an error handler for any kind of errors. If you can distinguish the errors related to duplicates (unique key violation) with enough details, then you do not need the CATCH block in SQL. You will have a series of catch in C#.
daowdos 296 Reputation points

2021-08-25T10:32:24.167+00:00

I fixed my question with this sentence

Answer accepted by question author

1 additional answer

Your answer

EchoLiu-MSFT 14,626 Reputation points

2021-08-25T06:25:19.087+00:00

Hi @Elado,

Sorry, your question is not clear enough for me. Please help clarify the meaning of this sentence.

Regards
Echo
Viorel 126.9K Reputation points

2021-08-25T07:04:11.727+00:00

I think that your client ASP code should have an error handler for any kind of errors. If you can distinguish the errors related to duplicates (unique key violation) with enough details, then you do not need the CATCH block in SQL. You will have a series of catch in C#.
daowdos 296 Reputation points

2021-08-25T10:32:24.167+00:00

I fixed my question with this sentence

Answer 1

Erland Sommarskog 134K MVP Volunteer Moderator

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.

daowdos 296 Reputation points

2021-08-25T11:06:33.283+00:00

Should I do it with extra check or IF NOT EXISTS is enough ? -
BEGIN CATCH SET @error = "Email address is already exist" SELECT @error END CATCH
Tom Phillips 17,786 Reputation points

2021-08-25T13:38:43.397+00:00

There are other errors which can cause the CATCH to be called. Not all errors will be due to the email address already existing.
AgaveJoe 31,361 Reputation points

2021-08-25T14:17:09.387+00:00

In my opinion, you're overly complicating the logic. I would create a query/procedure that checks if the email exists. Prompt the user if the email exists otherwise; call another procedure to add the user record.

Designing procedures/methods that do one thing well are much easier to maintain and debug. For example, updating email address. The same steps are required. First check if the email address exists. Do an update if the email address is not found otherwise; prompt the user. You've already written the email verification logic so all you have to do is write an UPDATE procedure.

I think it is worth mentioning that the ASP.NET Identity API has the feature you are implementing.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2021-08-25T20:49:49.747+00:00
Should I do it with extra check or IF NOT EXISTS is enough ?

In practice, yes. There is a theoretical possibility that a concurrent user sticks in the same address in the same millisecond.

But in many cases you would not code for this possibility; you would only do it, if it proves to constitute a real problem.

But as a principle, you should have a CATCH handler that goes:

END CATCH IF @@trancount > 0 ROLLBACK ; THROW BEGIN CATCH

And, yes, there should be a semicolon before THROW.
daowdos 296 Reputation points

2021-08-25T21:03:34.56+00:00

Why is it important to do ROLLBACK if -@@trancount > 0

I didn’t understand the “open transactions” thing.
Erland Sommarskog 134K Reputation points MVP Volunteer Moderator

2021-08-25T21:13:46.033+00:00

If you exit the procedure with an open transaction (and don't handle it in the client), it will remain open which can cause lots of problems. You will retain locks you have acquired and block other users. If you proceed and perform other updates, and later encounter an error that by itself rolls back the transaction, you will lose those updates. The same happens if you disconnect the session later.

Such transactions are known as orphaned transactions, and believe me, you don't want them. They causes a lot of pain.

Answer 2

Pet Loki 1

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

daowdos 296 Reputation points

2021-08-24T19:53:08.627+00:00

How can I -

keep track of its successful usage so you won't use them again ?

Is my procedure written right ?

Share via

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

1 additional answer

Your answer