question

Doawdos avatar image
0 Votes"
Doawdos asked ErlandSommarskog commented

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

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-transact-sqlsql-server-reporting-servicesdotnet-sqlclient
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Elado,

126219-image.png

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

Regards
Echo


0 Votes 0 ·
image.png (9.4 KiB)
image.png (9.2 KiB)

I fixed my question with this sentence

0 Votes 0 ·

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#.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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


0 Votes 0 ·

There are other errors which can cause the CATCH to be called. Not all errors will be due to the email address already existing.

1 Vote 1 ·

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.

1 Vote 1 ·

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


I didn’t understand the “open transactions” thing.

0 Votes 0 ·
Show more comments

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.

0 Votes 0 ·
PetLoki-6899 avatar image
0 Votes"
PetLoki-6899 answered Doawdos commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

How can I -

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

Is my procedure written right ?

0 Votes 0 ·