Alan.B - Wednesday, March 7, 2018 9:11 AM

Some code formatting food for thought...
For cases where I need to repeat the same expression multiple times you can create on "on-the-fly" inline function to simplify your code. In Lynn's code this expression is repeated a couple times:
CHARINDEX('-',REVERSE(@VendItemNumber)

You can simplify the code using the Table Value constructor like this:
SELECT LEFT(f.vi,LEN(f.vi) - f.pos), RIGHT(f.vi, f.pos - 1)
FROM (VALUES (CHARINDEX('-',REVERSE(@VendItemNumber)), @VendItemNumber)) f(pos,vi); 

For situations with longer expressions that are called several times you will see much cleaner code and without any performance penalty. In fact, there are times where you will see a performance improvement in cases where the optimizer is not smart enough to realize that it only needs to calculate the same expression once.

There is no performance penalty for doing this, actually - there are times where you will see a performance improvement. For example, run this will "include actual execution plan" turned on:

-- sample data 
CREATE TABLE #sometable(someid int identity, somevalue decimal(10,2));
INSERT #sometable(somevalue) VALUES (100),(1050),(5006),(111),(4);
GO

DECLARE @var1 int = 100, @var2 int = 50, @var3 int = 900, @topPct tinyint = 90;
-- version 1 with repeated formula
SELECT TOP (@topPct) PERCENT
someid,
    somevalue,
    someCalculation =
  CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END,
someRank = dense_rank() OVER (ORDER BY
  CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END)
FROM #sometable
WHERE CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END
BETWEEN 900 AND 2000
ORDER BY -- simulate another event that causes a sort
  CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3 ELSE @var3+somevalue END;

-- version 2 with inline alias
SELECT TOP (@topPct) PERCENT
someid,
somevalue,
someCalculation = itvf.result,
someRank   = dense_rank() OVER (ORDER BY itvf.result)
FROM #sometable
CROSS APPLY (VALUES(CASE WHEN @var3 < somevalue THEN (@var1/(@var2*2.00))+@var3
     ELSE @var3+somevalue END)) itvf(result)
WHERE itvf.result between 900 and 2000
ORDER BY itvf.result;