Is there a solution that I can perform purely in SQL

Yes. In a transaction, check for the row with the (UPDLOCK,HOLDLOCK) query hints.

eg

begin transaction

if not exists (
  select *
  from sometable with (updlock,holdlock)
  where Name = @SomeName
)
begin
  insert into sometable(...)   
end

commit transaction

This will lock the target row (or empty range) so if two sessions run this at the same time, one will be blocked on SELECT until the other commits the insert.

Assuming what you mean is that you want to actually see the result of that row:

You can actually do this in a single statement using MERGE .

DECLARE @found bit;

MERGE sometable WITH (UPDLOCK, HOLDLOCK) AS t
USING (VALUES (@Id, @SomeValue) ) AS v(Id, SomeValue)
  ON t.Id = v.Id
WHEN NOT MATCHED THEN
  INSERT (Id, SomeValue)
  VALUES (v.Id, v.SomeValue)
WHEN MATCHED THEN UPDATE
  SET @found = 1
OUTPUT inserted.*;

But given that MERGE is known to have bugs, I would still advise a separate IF NOT EXISTS ... INSERT and SELECT pairs.