Use the value "AS ColumnName" later in the request

Question

I am making a stored procedure where I need to use the value that was set earlier. I am explaining this rather poorly, 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 did CASE, which gives the result as Total. After that, I would like to use Total to calculate TotalWeight.

Sorry for my English.

Answer 1

The fact is that all the expressions in the list SELECTare evaluated in a way all at once. This is why you need to replicate your code. But you can create one subqueryfor this or ctehow:

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

CROSS APPLY, . : http://sqlmag.com/blog/tip-apply-and-reuse-column-aliases

Answer 3

, :

select col1, col2
from (select c1 * c2 + c3 as col1,
             ... as col2
      from tablename)
where col1 = ...

Answer 4

You can use cross apply

SELECT 
  t.Amount1 * x.Total * dbo.SelectReceptenLinkGewicht(t.Componentid,0) 
    AS TotalWeight
FROM table t
CROSS APPLY
(
  SELECT 
    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
) x