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

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.

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

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

Accepted answer

Erland Sommarskog 100.8K Reputation points MVP

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.
Please sign in to rate this answer.

2 people found this answer helpful.
daowdos 261 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,716 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 26,181 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 100.8K Reputation points MVP

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 261 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 100.8K Reputation points MVP

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.
Sign in to comment

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

1 additional answer