Select or insert

Imagine you have a dimension table and at some point you receive a value and don’t know if exists and in this case to return the @id of it or don’t exists and need to insert it .

Usually, this table should performed more selects then inserts.
So, the first statement will be to get the @id of it.

If the @id is null , meaning , this value don’t exists, then we need to insert it. So, the next step, is to insert it, and get the @id. This is the ideal case.

But what, another session/transaction/client is doing the same , with same value. And is a little faster, meaning that when you are still in “between” step1 and step2, he is already inserted. Then , a error is risen . See catch block for handle it.

In catch block, for particulars error numbers, the record exists now, and we are getting the @id back to client.

There are a few others way to deal with it, like table hint

CREATE PROCEDURE [dbo].[usp_dimMode_GetAndInsert]
(
@mddMode varchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @mddID as tinyint;


BEGIN TRY
    SELECT @mddID = mddID FROM dbo.dimMode WHERE mddMode = @mddMode;

    /* @mddID is null,  insert*/
    IF @mddID IS NULL
    BEGIN
        INSERT INTO dbo.dimMode(mddMode)
        VALUES(@mddMode);

        SELECT @mddID = SCOPE_IDENTITY();
    END

    RETURN @mddID;

END TRY
BEGIN CATCH

  -- ignore duplicate key errors, throw the rest.
  IF ERROR_NUMBER() IN (2601, 2627)
  BEGIN
        SELECT @mddID = mddID FROM dbo.dimMode WHERE mddMode = @mddMode;

        RETURN @mddID;
  END

END CATCH
END

S