CODEWITHSUNDEEP
sqlsql-server
Lefty
Lefty

Reputation: 391

SQL - store Function output for multiple uses

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

Upvotes: 0

Views: 91

Answers (3)

iSR5
iSR5

Reputation: 3498

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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.

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

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

Upvotes: 3

Related Questions