Ndtyjky

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.