SQL Server : how to declare variable in condition case when
<p>I have a problem when running queries with a 'case when'. so I want to create a variable to hold the value of the results of the query 'select' to other tables. like the example below. please help me to get the results I want. thank you.</p>

<pre><code>SELECT 
    a.field1, a.field2, 
    a.field3 =
      CASE 
         WHEN a.field1 = 'alfa' 
            THEN
         WHEN
            -- I want to declare variable to check get value from another table with query select and condition 'WHERE' with variable declare
var varTest = (SELECT TOP 1 b.field1 FROM Table2 as b WHERE b.field2=a.field2) Then
    if varTest = 'actif' then
    SELECT (c.field4 * C.field5) as hasil FROM Table2 as c WHERE c.field1=varTest)    
       ELSE 
           a.field3 
    END                                                         
FROM  
    Table1 a(NOLOCK)  
WHERE 
    a.field1 = 'alfa'
</code></pre>

<p>This is sample data :</p>

<pre><code>field1 | field2 | field3 | &gt; Table1
alfa     idAlfa      0
beta     idBeta      0
carlie   idCarlie    0


field1 | field2 | field4 | field5 | &gt; Table2
actif    idAlfa     80       5
pasif    idBeta     50       5
other    idCarlie   10       5
</code></pre>

<p>Result :</p>

<pre><code>field1 | field2  | field3
alfa     idAlfa    400
beta     idBeta    250
carlie   idCarlie  50
</code></pre>