Pass a function return to another in the same row

问题:

I Need to pass the return value of a function that is selected in previous column and pass it as parameter to a following function in the same row. I cannot use the alias:

What I want to have is:

SELECT
    dbo.GetSecondID(f.ID) as SecondID,
    dbo.GetThirdID(SecondID) as ThirdID
FROM Foo f

Any workaround? Thank you!

EDIT:

The method dbo.GetSecondID() is very heavy and I am dealing with a couple of million records in the table. It is not wise to pass the method as a parameter.

回答1:

The way that SQL is designed, it is intended that all columns can be computed in parallel (in theory). This means that you cannot have one column's value depend on the result of computing a different column (within the same SELECT clause).

To be able to reference the column, you might introduce a subquery:

SELECT SecondID,dbo.GetThirdID(SecondID) as ThirdID
FROM
(
    SELECT
        dbo.GetSecondID(f.ID) as SecondID   
    FROM Foo f
) t

or a CTE:

;WITH Results1 AS (
    SELECT
        dbo.GetSecondID(f.ID) as SecondID   
    FROM Foo f
)
SELECT SecondID,dbo.GetThirdID(SecondID) as ThirdID
FROM Results1

If you're building up calculations multiple times (e.g. A depends on B, B depends on C, C depends on D...), then the CTE form usually ends up looking neater (IMO).

回答2:

Bingo! The secret stand in applying a CROSS APPLY. The following code was helpful

SELECT
    sndID.SecondID,
    dbo.GetThirdID(sndID.SecondID) as ThirdID
FROM Foo f
CROSS APPLY
(
    SELECT dbo.GetSecondID(f.ID) as SecondID
) sndID

EDIT:

This only works if SecondID is unique (only one record is returned) or GROUP BY is used

回答3:

Did you mean this:

SELECT
     dbo.GetThirdID(dbo.GetSecondID(f.ID)) as ThirdID
FROM Foo f