I believe this code:

 

IF NOT EXISTS (SELECT 1 FROM MyTable WHERE MyField = @Value)

    BEGIN

        INSERT MyTable(My Field) VALUES (@Value)

        RETURN 1

    END

ELSE

    BEGIN

        RAISERROR('My bespoke PK Error',10,1)

        RETURN 0

END

is still flawed (for The Error trapping purpose that was intended to)

Let's suppose we are in a highly transactional environment and two procedures are executing the same code. On the "Exists" clause both get the "false" but then one of them will insert the PK that maybe the other will try too! Then on the second procedure the insert will raise the "avoided" error to the client. You could have placed a lock but that would be even worse

I would rather rewrite it as:

  INSERT MyTable(My Field) VALUES Where MyField <> @Value



IF @@ROWCOUNT = 0 

 RAISERROR('My bespoke PK Error',10,1)

But in any case, the solution will really be:

Microsoft, Please

STRUCTURED EXCEPTION HALNDLING!!!!!!