SQL Assign variable to function result within SELECT Statement

Question

I have a SELECT statement that uses the result of a function multiple times. Instead of repetitively tapping the function, is there a way that I can assign a variable to it in order to make the statement more efficient and I don't have to keep tapping the function?

Overly Simplified Hypothetical Example Code:

DECLARE @Total Decimal(18,2)

SELECT t.ClientName AS Customer, t.ClientID,
--This function returns total sales for the Client
@Total = dbo.functionSalesTotal(t.ClientID), 
@Total AS Sales, (@Total * 0.13) AS Tax, 
(@Total + (@Total *0.13)) AS TotalIncludingTax
FROM table t

Gordon Linoff · Accepted Answer · 2017-10-11 00:55:18Z

1

A subquery or apply is the best approach:

SELECT t.ClientName AS Customer, t.ClientID, 
       v.total AS Sales, (v.Total * 0.13) AS Tax, 
       (v.Total + (v.Total *0.13)) AS TotalIncludingTax
FROM table t CROSS APPLY
     (VALUES (dbo.functionSalesTotal(t.ClientID)) as v(total);

answered Oct 11, 2017 at 0:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stanislav Kundii Over a year ago

Function may by deterministic

Collectives™ on Stack Overflow

SQL Assign variable to function result within SELECT Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related