What happens if I Select SCOPE_IDENTITY() after an insert fails

If no Identity is inserted SCOPE_IDENTITY() will return null, you can check for the condition you specify by ...

stackoverflow.com