This is not RDBMS at all. You are writing 1970's mag tape files in SQL. You need to stop and do a correct data model. We have no DDL or specs with which to help you, tho. Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements.
And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells
I have a stored procedure that is passed an ID [sic] of a row [sic]. If that ID [sic] can't be found (i.e., the select returns no rows) it will insert a new record [sic] and then return that row via a select. I think the logic is right, but if the ID can't be found it isn't returning anything. <<
There is no generic “id” in RDBMS. Rows are not records. We retrieve them with a key. Why do you think that the count of physical insertion attempt (not even successes!) is a valid attribute of an entity? Answer: it is exactly what the record number on a mag tape is!
ISO does not allow spaces in data element name, but old COBOL programmers do this so they can keep display formatting in the same tier, like they do with monolithic COBOL programs.
I see you declare local variables; but we hate local variables in a declarative language like SQL. In RDBMS, a data element has “”; there is no generic “status”, “id”, “system”, etc. They have to be "_status”, "_id”, "_system”, etc.
Unlike the records in COBOL, a column is part of the whole schema. It does not have a local hierarchy to define it. You need to design the data and come up with an encoding for the “project_reference” that can be validated and verified.
Maybe a CREATE SEQUENCE could help, but who knows?