CODEWITHSUNDEEP
sqlsql-server
Ankit Jain
Ankit Jain

Reputation: 1286

Call Function only single time for multiple places in a query

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.

Upvotes: 5

Views: 914

Answers (2)

Devart
Devart

Reputation: 122042

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'

Upvotes: 9

Radhamani Muthusamy
Radhamani Muthusamy

Reputation: 328

Declare variable and try it

DECLARE @totalPackagedQty int;
set @totalPackagedQty=dbo.TotalPackagedQty(t1.int_PackingListDetailId);
Then use totalPackagedQty at all the places instead of function call.

Upvotes: -2

Related Questions