title: Assign value to variable in SELECT statement
body: <p>I have this SQL Server query:</p>
  
  <pre><code>DECLARE @HoursUsed float
  
  SELECT COLUMN_A 
  ,(SELECT (@HoursUsed = (SELECT dbo.fn_GetProjectHoursUsed(Project.fg_projectId))) 
  ,@HoursUsed*100
  ,@HoursUsed*200
  FROM Project, OTHER_TABLES)
  </code></pre>
  
  <p>I need the result from the function to be added to the variable because I will need it in several places later in the query.</p>
  
  <p>The DB function is working fine by itself. If I remove the variable from the <code>SELECT</code> it works fine.</p>
  
  <p>If I try to run the query like this it throws errors like:</p>
  
  <blockquote>
    <p>Incorrect syntax near =, ), as.</p>
  </blockquote>
  
  <p>The entire query uses this function several times and I have performance issues with it. So that's why I want to put the value in a variable and call the function only once.</p>
  
  <p>I appreciate your help</p>
  
  <p>Edit:
  Here is the complete query.</p>
  
  <pre><code>DECLARE @HoursUsed float
  
  SELECT  DISTINCT
      Account.Name as AccountName
      ,Project.FG_SalesRepIdName as Rep
      ,Project.fg_Name as ProjectName
      ,Project.fg_projectId
  
  ,(SELECT dbo.fn_GetProjectPriceRate(Project.fg_projectId)) as ProjectPriceRate
  
  ,(
  SELECT SalesOrder.totalAmount FROM fg_Project, SalesOrder
          WHERE fg_Project.fg_orderId = SalesOrder.salesOrderId 
              AND fg_Project.fg_Name = Project.fg_Name
              AND fg_Project.fg_projectId = Project.fg_projectId
  )/dbo.fn_GetProjectPriceRate(Project.fg_projectId) as TotalHours  
      --The total hours are found by division of the total price of the project     with the price rate
      ,(
      SELECT SalesOrder.totalAmount FROM fg_Project, SalesOrder
              WHERE fg_Project.fg_orderId = SalesOrder.salesOrderId 
                  AND fg_Project.fg_projectId = Project.fg_projectId
      ) AS TotalAmount
  
      ,(SELECT @HoursUsed = dbo.fn_GetProjectHoursUsed(Project.fg_projectId)
          SELECT @HoursUsed
      ) as HoursUsed
  
      ,(
      (SELECT dbo.fn_GetProjectHoursUsed(Project.fg_projectId))
      /
          NULLIF(((SELECT SalesOrder.totalAmount FROM fg_Project, SalesOrder
              WHERE fg_Project.fg_orderId = SalesOrder.salesOrderId 
                  AND fg_Project.fg_Name = Project.fg_Name
                  AND fg_Project.fg_projectId = Project.fg_projectId
                 )
          /
                NULLIF(dbo.fn_GetProjectPriceRate(Project.fg_projectId),0)
               ),0)
      )*100 as PercentHoursUsed
      ,(SELECT dbo.fn_GetProjectHoursBilled(Project.fg_projectId)) as HoursBilled
      ,(SELECT dbo.fn_GetProjectHoursBilled(Project.fg_projectId))
       *
       (SELECT dbo.fn_GetProjectPriceRate(Project.fg_projectId)) as AmountBilled
  
  
  
  FROM Account, fg_Project as Project, fg_ProjectElement, Product
  
  WHERE       Account.accountId = Project.fg_regardingId
      AND Project.fg_ProjectId = fg_ProjectElement.fg_projectElemenstId
      AND fg_ProjectElement.fg_ProductId = Product.ProductId
      AND Product.ProductNumber = 'WEB DEV'
  </code></pre>