title: Use select statement as input for stored procedure. T-SQL
body: <p>I have the following problem;</p>
  
  <p>I currently have a stored procedure and when I enter 4 different parameters is does a lot of calculations and then a select statement to output the results (11 columns) like this:</p>
  
  <p><a href="https://i.stack.imgur.com/ix8tc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ix8tc.png" alt="usp"></a></p>
  
  <p>The starting of this query looks like this:</p>
  
  <pre><code>    ALTER PROCEDURE [AGG].[usp_CalculateIsentropeEfficiency] 
  (
      @InlaatdrukE INT,
      @InlaattempC INT,
      @UitlaatdrukE INT,
      @UitlaattempC INT
  )
  AS
  
  
  
  /*-------------------------- Omrekenen van druk naar absolute druk en temperatuur naar de eenheid Kelvin. --------------------------*/
  DECLARE @InlaatdrukA DECIMAL(20,15)
  DECLARE @InlaattempK DECIMAL(20,15)
  DECLARE @UitlaatdrukA DECIMAL(20,15)
  DECLARE @UitlaattempK DECIMAL(20,15)
  
  SET @InlaatdrukA = @InlaatdrukE + 1.01325
  SET @InlaattempK = @InlaattempC + 273.15
  SET @uitlaatdrukA = @UitlaatdrukE + 1.01325
  SET @UitlaattempK = @UitlaattempC + 273.15
  </code></pre>
  
  <p>After this part, I declare and set a couple more variables. Some looking up values in a lookup table and some just plain calculations. </p>
  
  <p>The idea now is that these different parameters should be filled according to another select statement I did with a pivot. </p>
  
  <p>The output of this pivot table looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/ixgjT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ixgjT.png" alt="pivottable"></a></p>
  
  <p>Where <code>PI,PU,TI</code> and <code>TU</code> are the inputs for the stored procedure. </p>
  
  <p>My issue is I can't run the stored procedure for each and every row, so I was thinking to put the Pivot Select in the stored procedure to create a bigger table. This also doesn't work as I cant use the pivot table as input for variables where I do calculations with.</p>
  
  <p>The desired output needs to be pivot table + calculations. like this:</p>
  
  <p><code>TimeKey Component PI PU TI TU Q Row# InlaatdrukA InlaattempK Tuit_s</code>, etc.</p>
  
  <p>I hope anyone of you knows how to do this best, as I believe a stored procedure may not be the best option. </p>
  
  <p>Thank you for sharing your expertise.</p>
  
  <p>As asked in the comments here is more SQL code with the logic:
  In the procedure:</p>
  
  <pre><code>--Inlaat
  DECLARE @Lookup_inlaat_templower INT
  DECLARE @Lookup_inlaat_druklower INT
  DECLARE @Lookup_inlaat_temphigher INT
  DECLARE @Lookup_inlaat_drukhigher INT
  
  SET @Lookup_inlaat_templower = (SELECT MAX(ref.Temperature) FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Temperature &lt;= @InlaattempK)
  SET @Lookup_inlaat_druklower = (SELECT MAX(ref.Pressure) FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Pressure &lt;= @InlaatdrukA)
  SET @Lookup_inlaat_temphigher = (SELECT MIN(ref.Temperature) FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Temperature &gt;= @InlaattempK)
  SET @Lookup_inlaat_drukhigher = (SELECT MIN(ref.Pressure) FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Pressure &gt;= @InlaatdrukA)
  
  --Uitlaat
  DECLARE @Lookup_uitlaat_templower INT
  DECLARE @Lookup_uitlaat_druklower INT
  DECLARE @Lookup_uitlaat_temphigher INT
  DECLARE @Lookup_uitlaat_drukhigher INT
  
  SET @Lookup_uitlaat_templower = (SELECT MAX(ref.Temperature) FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Temperature &lt;= @uitlaattempK)
  SET @Lookup_uitlaat_druklower = (SELECT MAX(ref.Pressure) FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Pressure &lt;= @uitlaatdrukA)
  SET @Lookup_uitlaat_temphigher = (SELECT MIN(ref.Temperature) FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Temperature &gt;= @uitlaattempK)
  SET @Lookup_uitlaat_drukhigher = (SELECT MIN(ref.Pressure) FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Pressure &gt;= @uitlaatdrukA)
  
  /*-------------------------- Interpolatie van de gemeten inlaat temperatuur en druk. --------------------------*/
  DECLARE @Isentrope_inlaat_lowertemp_lowerdruk DECIMAL(19,17)
  DECLARE @Isentrope_inlaat_lowertemp_higherdruk DECIMAL(19,17)
  DECLARE @Isentrope_inlaat_highertemp_lowerdruk DECIMAL(19,17)
  DECLARE @Isentrope_inlaat_highertemp_higherdruk DECIMAL(19,17)
  
  SET @Isentrope_inlaat_lowertemp_lowerdruk = (SELECT ref.IsentropeCoefficient FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Temperature = @lookup_inlaat_templower AND ref.Pressure = @lookup_inlaat_druklower)
  SET @Isentrope_inlaat_lowertemp_higherdruk = (SELECT ref.IsentropeCoefficient FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Temperature = @lookup_inlaat_templower AND ref.Pressure = @lookup_inlaat_drukhigher)
  SET @Isentrope_inlaat_highertemp_lowerdruk = (SELECT ref.IsentropeCoefficient FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Temperature = @lookup_inlaat_temphigher AND ref.Pressure = @lookup_inlaat_druklower)
  SET @Isentrope_inlaat_highertemp_higherdruk = (SELECT ref.IsentropeCoefficient FROM AGG.tblRefIsentropeEfficiency ref WHERE ref.Temperature = @lookup_inlaat_temphigher AND ref.Pressure = @lookup_inlaat_drukhigher)
  
  DECLARE @IsentropeInlaatG1 DECIMAL(19,17)
  DECLARE @IsentropeInlaatG2 DECIMAL(19,17)
  
  SET @IsentropeInlaatG1 = ((@lookup_inlaat_druklower + @Deltax - @InlaatdrukA) * @Isentrope_inlaat_lowertemp_lowerdruk + (@InlaatdrukA - @lookup_inlaat_druklower) * @isentrope_inlaat_lowertemp_higherdruk) / @deltax
  SET @IsentropeInlaatG2 = ((@lookup_inlaat_druklower + @Deltax - @InlaatdrukA) * @Isentrope_inlaat_highertemp_lowerdruk + (@InlaatdrukA - @lookup_inlaat_druklower) * @isentrope_inlaat_highertemp_higherdruk) / @deltax
  </code></pre>
  
  <p>It is just setting of variables and doing calculations with them. (this is still not everything but should give more insight in how i calculate the fields. The ending of the stored procedure atm is just:</p>
  
  <pre><code>SELECT  InlaatdrukA = @InlaatdrukA, 
          InlaattempK = @InlaattempK, 
          UitlaatdrukA = @UitlaatdrukA, 
          UitlaattempK = @UitlaattempK,
          Interpolate_BL = @InlaatInterpolate_BL,
          Tuit_s = @Tuit_s,
          H1_Interpolate_BL = @H1_Interpolate_BL,
          H2_Interpolate_BL = @H2_Interpolate_BL,
          H2s_Interpolate_BL = @H2s_Interpolate_BL,
          IsentropeEfficiency = @IsentropeEfficiency,
          IsentropeInefficiency = @IsentropeInefficiency
  </code></pre>
  
  <p>And the entire Pivot SQL query is this:</p>
  
  <pre><code>with  cte_src as (    
  SELECT  
  VH.Value,    
  VH.TimeKey,   
  RIGHT(M.MeasurementOriginalName, CHARINDEX('.', REVERSE(M.MeasurementOriginalName))-1) AS Type,      
  SUBSTRING(M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, 1))+1)+1)+1,charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, 1))+1)+1)+1) - charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, charindex('.', M.MeasurementOriginalName, 1))+1)+1)-1)  as Component         
  
  FROM [MSTR].[tblDimMeasurement] M      
  INNER JOIN [AGG].[tblTmpFactMeasurementAnalogueValueHour] VH ON VH.MeasurementKey = M.MeasurementKey        
  
  WHERE M.MeasurementOriginalName LIKE ('%A-414-C014%') 
  and RIGHT(M.MeasurementOriginalName,2) IN ('PU','PI','TI','TU','.Q')           ),
  
  cte_piv as(
  select *, ROW_NUMBER() OVER (ORDER BY TimeKey) as Row# from cte_src  
  PIVOT(  MAX(Value)  FOR TYPE IN ([PI],[PU],[TI],[TU],[Q])  )Pivotname  
  
  )
  
  
  Select * from cte_piv
  </code></pre>
  
  <p>here i select the fields i need and with charindex i make sure i get the right part of the string.</p>
  
  <p>Then pivot it so i get the outcome with <code>PI, PU</code> etc as shown above.</p>