CREATE TABLE myTable (pKey INT IDENTITY(1,1) PRIMARY KEY,
 CODE VARCHAR(10));
DECLARE @MYCODE VARCHAR(10);
SET @MYCODE = 'MYCODE';

DECLARE @ID INT;

--- Option 1 Using ROWCOUNT
SELECT @ID = pKey FROM myTable (NOLOCK) WHERE CODE=@MYCODE;
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO myTable (CODE)
    VALUES (@MYCODE);
    SET @ID=@@IDENTITY;
END

-- Option 2 
SELECT @ID = pKey FROM myTable WHERE CODE=@MYCODE;
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO myTable (CODE)
    VALUES (@MYCODE);
    SELECT @ID=SCOPE_IDENTITY();
END

--- Option 3 Using EXISTS
IF NOT EXISTS (SELECT * FROM myTable WHERE CODE=@MYCODE)
BEGIN
    INSERT INTO myTable (CODE)
    VALUES (@MYCODE);
END
SELECT @ID=pKey FROM myTable WHERE CODE=@MYCODE;

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Declare @ids Table (id INT);

IF NOT EXISTS (SELECT * FROM myTable WHERE CODE=@MYCODE)
BEGIN
    INSERT INTO myTable (CODE)
    OUTPUT Inserted.id into @ids
    VALUES (@MYCODE);

    Select @id = id from @ids
END

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DECLARE @count int, @id int
SET @Count = (SELECT count(*) from myTable WHERE Code = @MyCode) -- count all rows to see if there are any... Isn't there another function for that?
IF @Count <= 0
  BEGIN
    Insert into myTable (Code)
    Values (@MyCode)

    SET @id = MAX(id) FROM MyTable -- race condition, what race condition???
  END

SET @id = (SELECT id FROM MyTable WHERE Code = @MyCode) -- let's hope there's a unique constraint. Oh wait, no constraints.

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DECLARE @MYCODE int = NULL
DECLARE @ID int = NULL
DECLARE @OUTPUT TABLE (ID int)

MERGE dbo.myTable WITH (HOLDLOCK) AS t
USING (VALUES(@MYCODE)) AS u (MyCode)
    ON EXISTS (SELECT t.CODE INTERSECT SELECT u.MyCode)
WHEN NOT MATCHED THEN INSERT (CODE) VALUES (@MYCODE)
WHEN MATCHED THEN UPDATE SET @ID = t.pKey
OUTPUT INSERTED.pKey INTO @OUTPUT (ID);

SELECT 
    CASE 
        WHEN @ID IS NOT NULL THEN @ID 
        ELSE (SELECT TOP(1) ID FROM @OUTPUT) 
    END