Call Function only single time for multiple places in a query

Question

SQL Query

SELECT
    dbo.TotalPackagedQty(t1.int_PackingListDetailId),
    float_Quantity,
    int_PackingListDetailId
        CASE
            WHEN dbo.TotalPackagedQty(t1.int_PackingListDetailId) = 0 THEN 1
            WHEN dbo.TotalPackagedQty(t1.int_PackingListDetailId) < float_Quantity THEN 2
            WHEN dbo.TotalPackagedQty(t1.int_PackingListDetailId) = float_Quantity THEN 3
        END
FROM tblSdPackingListDetail t1
WHERE int_PackingId = '10901032014121313496PM0'

This is a sql query.

I am using a function with the name dbo.TotalPackagedQty(t1.int_PackingListDetailId).

I am using this function 4 times in query. Output is coming correct, but i want to call this function only single time to increase speed of the query.

Kindly suggest me a better option in query not stored procedure or other things.

Answer 1

Try this one -

SELECT
    value,
    float_Quantity,
    int_PackingListDetailId =
                CASE
                    WHEN value = 0 THEN 1
                    WHEN value < float_Quantity THEN 2
                    WHEN value = float_Quantity THEN 3
                END
FROM dbo.tblSdPackingListDetail t1
CROSS APPLY (
    SELECT value = dbo.TotalPackagedQty(t1.int_PackingListDetailId)
) tt
WHERE int_PackingId = '10901032014121313496PM0'