If Exists - Select Prevent Repeating Code, how to solve?

 Unfortunately, I'm afraid by trying to prevent repeating code you might end up in writing more code and making the query less readable, you can solve sql server if there is an error. But I believe this can be advantageous for you - take into account situations where in the test you would only have to search in one or two tables, where in order to retrieve all the details you will have to join to several more. In those situations you can simplify the test query thus also making them quicker to execute. And while we are at it, you might also consider changing your WHERE statement. Using functions in the predicate makes them not SARGable and prevents using indexes in those cases. A better approach in this example would be to check

WHERE OrderNumber LIKE 'ABC%'
as the optimizer will perform conversion to something along the lines of
OrderNumber >= 'ABC' AND OrderNumber < 'ABD'
which supports the use of indexes. So this would be Index Scan (using left()) versus Index.