Odtnhj

I'm performing a function multiple times within my Select statement:

Select Substring(AccNo,5,3),

Case When Substring(AccNo,5,3)='ABC' then
 ABC'
Else
 'Other'
End as AType

Case When Substring(AccNo,5,3)='DEF' then
 'DEF'
Else
 'Other'
End as Btype 

From MainTable

In reality, my code does about 20 different operations on the same Function output and it is more complicated than this example - but the principle stands.

In most languages, I would just perform the Function ONCE and store the result in a variable, to save unnecessary processing. I'm having trouble finding how I would do something like that in SQL - or do I assume that SQL Server is very smart and knows it only has to perform the function once and store the result?

Set Result=Substring(AccNo,5,3)

Case When Result='ABC'
etc

I think you need

SELECT CASE WHEN Res = 'ABC' THEN 'ABC' ELSE 'Other' END AType,
 CASE WHEN Res = 'DEF' THEN 'DEF' ELSE 'Other' END BType
FROM
(
SELECT Substring(AccNo,5,3) Res --You write SUBSTRING() just one time
From MainTable
) T

I don't think this is possible within a single query. I had thought apply would do the trick:

Select str,
 (Case When str = 'ABC' then 'ABC' Else 'Other'
 End) as AType,
 (Case When str = 'DEF' then 'DEF' Else 'Other'
 End) as BType
From MainTable mt cross apply
 (values (Substring(AccNo, 5, 3)) v(str);

But it doesn't. Subqueries and CTEs also have no guarantee that the function is called only once per row.

EDIT:

I'm leaving the answer, but there is no guarantee. SQL Server reserves the right to rearrange operations, so even this:

select *
from (values (1), (2), (3)) v(x) cross apply
 (select newid() as y) y

Calls newid() three times. See here.

Not sure what is your expected output, but if you need to have a column for each SUBSTRING(AccNo,5,3) type. It's possible by using PIVOT here is a quick example :

DECLARE 
 @t TABLE (AccNo VARCHAR(50), AccName VARCHAR(50))

INSERT INTO @t VALUES 
('8001ABC0011047', 'Personal'),
('3022DEF9028683', 'Internal'),
('6709GHI90217096', 'Local'),
('2014JKL17800222', 'International')

SELECT 
 AccNo
, ABC AType 
, DEF BType 
, GHI CType
FROM (
 SELECT *, SUBSTRING(AccNo,5,3) AccountType
 FROM @t
) D
PIVOT(
 MAX(AccountType) 
 FOR AccountType IN(ABC, DEF, GHI, JKL)
) PV 

If you're using it too often in other queries, you can use a scalar function, something like :

CREATE FUNCTION GetAccountType
(
 @AccNo VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN

 RETURN 
 CASE SUBSTRING(@AccNo,5,3) 
 WHEN 'ABC' THEN 'ABC'
 WHEN 'DEF' THEN 'DEF'
 ELSE 'Other'
 END 

END

And then you call it like this :

SELECT dbo.GetAccountType(AccNo) AccountType 
FROM @t

This would be a more code reuse approach.