1

We receive an order list, and validate it starts with certain prefixes, example if OrderNumber does not start with ABC, than display order list to 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 anyway 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
4

Unfortunately I'm afraid by trying to prevent repeating code you might end up in writing more code and making the query less readable. But I believe this can be advantageous for you - take into account situations where in the test you would only have to search in a 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 prevent 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 Seek (using LIKE).

2

Unfortunately, for a variety of reasons (not just legacy) SQL does not have nearly all of the code re-use options and features that most client languages have and some of them (like scalar UDFs) have significant problems. So to a large extent, if you want to be an effective SQL programmer, you have to resign yourself to to doing a lot more cut-and-paste code re-use than you would ever consider in a client language like C#, VB.Net or Java.

That said, you can eliminate the redundancy in your example, however, whether it's actually better or not, you'll have to decide.

Declare @OrderErrorList Table(OrderNumber varchar(32));

Insert into @OrderErrorList 
select OrderNumber as OrderErrorList
from dbo.OrdersImport
where left(OrderNumber,3) <> 'ABC';

if exists
(
    select *
    from @OrderErrorList
)
begin
    select *
    from @OrderErrorList
end
else
  • Could you simplify this further by checking the @@ROWCOUNT instead of exists? – mathewb Jun 28 '18 at 14:46
  • @mathewb Yes, but it has to be done immediately after the INSERT..SELECT. If you add another statement then it suddenly stops working correctly and I try to avoid land mines like that in my answers. The workaround would be to save the @@ROWCOUNT in a variable, but then it would not be significantly better than what I have above. – RBarryYoung Jun 28 '18 at 20:49
0

Is there anyway in sql to get rid of the repetitive code?

Not really, no. You can reformulate the query like this, without the IF statement, but it's not really different from what you have:

SELECT  *
FROM    dbo.OrdersImport o
WHERE   LEFT(OrderNumber, 3) <> 'ABC'
    AND EXISTS (
        SELECT *
        FROM dbo.OrdersImport
        WHERE LEFT(OrderNumber,3) <> 'ABC'
        AND ID = o.ID
        )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy