If Exists - Select Prevent Repeating Code, how to solve?
We receive an order list, and validate it starts with certain prefixes, for example if OrderNumber does not start with ABC, then display the order list to the user as error. We use exists, to search for first existence, don't want to consume much performance time. If none exist, then we can start conducting other task in code. Is there any way in sql to get rid of the repetitive code? We have Many checks like this on multiple tables, trying to make code more efficient.
if exists ( select * from dbo.OrdersImport where left(OrderNumber,3) <> 'ABC' ) begin select OrderNumber as OrderErrorList from dbo.OrdersImport where left(OrderNumber,3) <> 'ABC' end else
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.