Use value of 'AS ColumnName' later in query

Question

I'm making a stored procedure where I need to use a value that has been set earlier. I'm pretty bad at explaining this so I will use an example:

  CASE 
     WHEN ((select top 1 stuksweergeven from componenten
            where componentid = componentlink.componentid) = 1) and
          ((select opbrengstperkilo from componenten
            where componentid = componentlink.componentid) <> 0) THEN 
        amount1 * (select opbrengstperkilo from componenten
                   where componentid = componentlink.componentid)
     ELSE 
        amount1
  END AS Total,
Amount1 * Total *(SELECT dbo.SelectReceptenLinkGewicht(Componentid,0)) AS TotalWeight

I made a CASE that gives it outcome as Total. After that i would like to use Total to calcute the TotalWeight.

Sorry for my English.

Answer 1

The thing is that all expressions in SELECT list are evaluated in all at once manner. That's why you need to replicate your code. But you can create subquery for that or cte like:

with cte as(
             select Amount1,
                    ComponentID,
                    CASE 
                       WHEN ((select top 1 stuksweergeven from componenten where componentid = componentlink.componentid) = 1) and  ((select opbrengstperkilo from componenten where componentid = componentlink.componentid) <> 0) 
                       THEN amount1 * (select opbrengstperkilo from componenten where componentid = componentlink.componentid)
                       ELSE amount1
                    END AS Total
             from SomeTable)

select Total,
       Amount1 * Total *(SELECT dbo.SelectReceptenLinkGewicht(Componentid,0)) AS TotalWeight
from cte

Or:

select Total,
       Amount1 * Total *(SELECT dbo.SelectReceptenLinkGewicht(Componentid,0)) AS TotalWeight
from (
             select Amount1,
                    ComponentID,
                    CASE 
                       WHEN ((select top 1 stuksweergeven from componenten where componentid = componentlink.componentid) = 1) and  ((select opbrengstperkilo from componenten where componentid = componentlink.componentid) <> 0) 
                       THEN amount1 * (select opbrengstperkilo from componenten where componentid = componentlink.componentid)
                       ELSE amount1
                    END AS Total
             from SomeTable) t

Answer 2

you can totally use CROSS APPLY to make things work for you. A very informative article: http://sqlmag.com/blog/tip-apply-and-reuse-column-aliases