We often encounter the "If not exists, insert" situation. [Dan Guzman's blog][1] has an excellent investigation of how to make this process threadsafe.

I have a basic table that simply catalogs a string to an integer from a `SEQUENCE`. In a stored procedure I need to either get the integer key for the value if it exists, or `INSERT` it and then get the resulting value. There is a uniqueness constraint on the `dbo.NameLookup.ItemName` column so data integrity isn't at risk but I don't want to encounter the exceptions.

It's not an `IDENTITY` so I can't get `SCOPE_IDENTITY` and the value could be `NULL` in certain cases.

In my situation I only have to deal with `INSERT` safety on the table so I'm trying to decide if it's better practice to use `MERGE` like this:

    SET NOCOUNT, XACT_ABORT ON;

    DECLARE @vValueId INT 
    DECLARE @inserted AS TABLE (Id INT NOT NULL)
     
    MERGE 
    	dbo.NameLookup WITH (HOLDLOCK) AS f 
    USING 
    	(SELECT @vName AS val WHERE @vName IS NOT NULL AND LEN(@vName) > 0) AS new_item
    		ON f.ItemName= new_item.val
    WHEN MATCHED THEN
    	UPDATE SET @vValueId = f.Id
    WHEN NOT MATCHED BY TARGET THEN
        INSERT
          (ItemName)
        VALUES
          (@vName)
    OUTPUT inserted.Id AS Id INTO @inserted;
    SELECT @vValueId = s.Id FROM @inserted AS s

I could do this witout using `MERGE` with just a conditional `INSERT` followed by a `SELECT`
I think that this second approach is clearer to the reader, but I'm not convinced it's "better" practice

    SET NOCOUNT, XACT_ABORT ON;
    
    INSERT INTO	
    	dbo.NameLookup (ItemName)
    SELECT
    	@vName
    WHERE
    	NOT EXISTS (SELECT * FROM dbo.NameLookup AS t WHERE @vName IS NOT NULL AND LEN(@vName) > 0 AND t.ItemName = @vName)
    
    DECLARE @vValueId int;
    SELECT @vValueId = i.Id FROM dbo.NameLookup AS i WHERE i.ItemName = @vName



Or perhaps there is another better way that I haven't considered 


  [1]: http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx