Should I just rely on Foreign Key Constraints for error handling?
Yes, this is a waste of time at least when your stored procedure is just the INSERT statement that may cause the foreign key violation. Where a foreign key is in place, validation of any attempted FK value is made against the reference table by the database engine itself.

If the value does not exist in the reference table, you will both receive an exception and have your INSERT statement aborted.

So, this would be perfectly enough:

CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

INSERT INTO ChildTable (ForeignKey_Id, Attribute)
VALUES (@ForeignKey_Id, @Attribute);


On the other hand, if your procedure is more than a single INSERT statement and you would like to abort the entire procedure on an invalid insert at its beginning, then preliminarily checking the FK value may not be entirely pointless. However, valid FK values would still be causing double lookups, and that is neither good nor necessary. To achieve the same effect of aborting the entire procedure, you could just put your procedure's body into a TRY/CATCH block:

CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

BEGIN TRY
    INSERT INTO ChildTable (ForeignKey_Id, Attribute)
    VALUES (@ForeignKey_Id, @Attribute);

    ... -- other statements
END TRY
BEGIN CATCH
    RAISERROR ('The input ForeignKey_Id does not exist', 16, 1);
    -- or you could catch ERROR_NUMBER(), ERROR_MESSAGE() etc.
    -- and re-raise the original exception
END CATCH;


More info:


TRY...CATCH (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)