title: SQL Server (T-SQL): Avoid call scalar function multiple times
body: <p>I have a stored procedure which does a <code>SELECT</code> to return some rows. Within the <code>SELECT</code>, I need to check a condition in order to return the correct value for some columns. This condition consists on a scalar function. All times scalar function is called with the same parameter for the row being processed, see below:</p>
  
  <pre><code>SELECT
      Id,
      Name,
      Surname,
      CASE WHEN (dbo.GetNumTravels(Id) &gt;= 50)
          THEN 10
          ELSE 99
      END as Factor1,
      CASE WHEN (dbo.GetNumTravels(Id) &gt;= 50)
          THEN 15
          ELSE -1
      END as Factor2,
      CASE WHEN (dbo.GetNumTravels(Id) &gt;= 50)
          THEN 30
          ELSE 70
      END as Factor3
  FROM 
      Employees
  WHERE 
      DepartmentId = 100
  </code></pre>
  
  <p>I am worried about performance, I mean, I do not like to call scalar function <code>dbo.GetNumTravels</code> multiples times, so how to avoid this and only call it once and then used it all the times I need it?</p>