title: Table value function to pass as parameter to generate output inside case statement
body: <p>My dataset looks like below</p>
  <p>SQL Server 2012.</p>
  <pre><code>DECLARE @ELECTRONICS TABLE
  (
      RESISTORID INT, 
      CAPACITORID VARCHAR(10),    
      VOLT  DECIMAL(6,2), 
      WATT  INT,  
      PASSIVENUMBER INT    
  ) 
  
  INSERT @ELECTRONICS
  SELECT 100, 'TH',   1.2,    5,  93 UNION ALL
  SELECT 200, 'TH',   1.2,    5,  93 UNION ALL
  SELECT 300, 'TH',   1.5,    5,  93 UNION ALL
  SELECT 100, 'TH',   -2.9,   5,  93 UNION ALL
  SELECT 500, 'RT',   3.3,    5,  93 UNION ALL
  SELECT 540, 'TH',   0,      5,  93 UNION ALL
  SELECT 540, 'SN',   3.3,    5,  93 UNION ALL
  SELECT 540, 'UL',   4.2,    5,  93 UNION ALL
  SELECT 800, 'TH',   -2.4,   5,  93 UNION ALL
  SELECT 300, 'RN',   2.2,    4,  35 UNION ALL
  SELECT 300, 'RN',   2.5,    6,  35 UNION ALL
  SELECT 100, 'RN',   1.2,    9,  35 UNION ALL
  SELECT 200, 'RN',   1.2,    9,  35 UNION ALL
  SELECT 300, 'RN',   1.5,    9,  35 UNION ALL
  SELECT 100, 'RN',   -2.9,   9,  35 UNION ALL
  SELECT 800, 'RN',   -2.4,   9,  31
  </code></pre>
  <p>I am trying to write a Table Valued UDF which accepts a parameter, <code>@PASSIVENUMBER</code>, and generates value for <code>POWERTHRESHOLD</code> and <code>RESISTORID</code> based on the <code>CAPACITORID</code>, <code>VOLT</code> and <code>WATT</code>.</p>
  <p>Function to be used in below SQL code.</p>
  <pre><code>SELECT *, CASE WHEN RESISTORID IN ('100','540') THEN 'A03' ELSE 'A01' END AS RESISTORID,
      CASE WHEN CAPACITORID = 'TH' AND VOLT &gt;0 THEN (VOLT * 1000) 
      WHEN CAPACITORID = 'TH' AND VOLT = 0 THEN (WATT * 1000)
      WHEN CAPACITORID &lt;&gt; 'TH' AND VOLT &gt;0 THEN VOLT
      ELSE WATT END AS POWERTHRESHOLD
  FROM @ELECTRONICS
  WHERE PASSIVENUMBER = 93
  
  SELECT * ,CASE WHEN RESISTORID IN ('300','800') THEN 'B03' ELSE 'B01' END AS RESISTORID,
      CASE WHEN CAPACITORID = 'RN' AND VOLT &gt;0 THEN (VOLT * 10*3/56) 
      WHEN CAPACITORID = 'RN' AND VOLT = 0 THEN (WATT * 100*2/21)
      WHEN CAPACITORID &lt;&gt; 'RN' AND VOLT &gt;10 THEN VOLT
      ELSE WATT END AS POWERTHRESHOLD
  FROM @ELECTRONICS
  WHERE PASSIVENUMBER = 35
  </code></pre>
  <p>Output when PASSIVENUMBER = 93</p>
  <pre><code>RESISTORID  CAPACITORID VOLT    WATT    PASSIVENUMBER   RESISTORID  POWERTHRESHOLD
  100 TH  1.20    5   93  A03 1200.00
  200 TH  1.20    5   93  A01 1200.00
  300 TH  1.50    5   93  A01 1500.00
  100 TH  -2.90   5   93  A03 5.00
  500 RT  3.30    5   93  A01 3.30
  540 TH  0.00    5   93  A03 5000.00
  540 SN  3.30    5   93  A03 3.30
  540 UL  4.20    5   93  A03 4.20
  800 TH  -2.40   5   93  A01 5.00
  </code></pre>
  <p>Output when PASSIVENUMBER = 35</p>
  <pre><code>RESISTORID  CAPACITORID VOLT    WATT    PASSIVENUMBER   RESISTORID  POWERTHRESHOLD
  300 RN  2.20    4   35  B03 2200.00
  300 RN  2.50    6   35  B03 2500.00
  100 RN  1.20    9   35  B01 1200.00
  200 RN  1.20    9   35  B01 1200.00
  300 RN  1.50    9   35  B03 1500.00
  100 RN  -2.90   9   35  B01 9.00
  </code></pre>
  <p>How to implement a function that checks a value with a case statement.</p>
  <p>Code I tried something like this is not giving proper result.</p>
  <pre><code>CREATE FUNCTION [dbo].[fnchkID]
  (
      @PASSIVENUMBER INT
  )
  RETURNS INT
  AS
  BEGIN
      RETURN
          CASE 
              WHEN CAPACITORID = RN and @VOLT&gt;0 THEN VOLT * 1000
              
              ELSE WATT from @ELECTRONICS
          END 
  END
  </code></pre>
  <p>Please share your thoughts.</p>