title: SQL Concatenate a Variable with a string inside of a select statement in a sproc
body: <p>I have a long and very repetitive query that I'm going to need to run every 5 minutes for reporting, there are a couple of excerpts from one of the select statements below, I have 26 columns that are similar to this that will need to be run for a total of 6 different lines, each with 7 different where statements, based on time frames, job, and operator, so rather than create a huge amount of T-SQL, and run it every five min I was thinking of trying to create a stored procedure with parameters passed to it for the two digit line number (in the example below '05'), the where conditions, and an identifier for the columns(In the example below 'Job'). The problem I'm having is concatenating the column names inside of the select statement, I've tried a number of methods none of which have worked.  </p>
  
  <p>Example (Yes I know lots of casting as decimal, I want to yell at myself for everything being varchar, but the application that transfers the data from our mfg machines to the Db will only write as string or datetime for timestamps, and the charindex's are because I've seen data being put in to the 20th decimal place, and in rare cases even further)</p>
  
  <pre><code>    ,AVG(CAST(LEFT("E05UP", (CHARINDEX(',', E05UP, 5) + 6)) AS decimal(11,6)))*100 AS JobE05Up
          ,AVG(CAST(LEFT(E05QUAL, (CHARINDEX(',', E05QUAL, 5) + 6)) AS decimal(11,6)))*100 AS JobE05Qual
          ,CASE WHEN AVG(CAST(LEFT(E05UP, (CHARINDEX(',', E05UP, 5) + 6)) AS decimal(11,6)))&gt;0 AND AVG(CAST(LEFT(E05SPD, (CHARINDEX(',', E05SPD, 5) + 6)) AS decimal(11,6)))&gt;0 AND 
              AVG(CAST(LEFT(E05QUAL, (CHARINDEX(',', E05QUAL, 5) + 6)) AS decimal(11,6)))&gt;0 THEN (AVG(CAST(LEFT(E05UP, (CHARINDEX(',', E05UP, 5) + 6)) AS decimal(11,6)))*
              (AVG(CAST(LEFT(E05SPD, (CHARINDEX(',', E05Spd, 5) + 6)) AS decimal(11,6)))/@E05SpeedMAX)*AVG(CAST(LEFT(E05QUAL, (CHARINDEX(',', E05QUAL, 5) + 6)) AS decimal(11,6)))*100)
              ELSE 0 END AS JobE05OEE
  </code></pre>
  
  <p>And what I'd be looking to do in short would be:</p>
  
  <pre><code>    SELECT AVG(E+@P1+SPD) AS @P2+Speed FROM Test.Dbo.Line+@P1 WHERE @P3
  </code></pre>
  
  <p>EDIT: Entire Query to select data from the last Db write only, after changing all of the Casts to a function, the only thing that will change between this and every other query for this report is the Line # (L05) the Column Name, the where clause, and the numbers being summed or averaged since we'll be working with more than one row.</p>
  
  <pre><code>DECLARE @L05CurWO as INT
  DECLARE @L05CurCount AS INT
  DECLARE @L05SpeedMAX AS INT
  DECLARE @E05SpeedMAX AS INT
  DECLARE @H05SpeedMAX AS INT
  DECLARE @P05SpeedMAX AS INT
  DECLARE @C05SpeedMAX AS INT
  DECLARE @L05CurQual AS Decimal(11,6)
  
  --Set Maximum Line Speeds Speeds
  SET @L05SpeedMAX = 147
  SET @E05SpeedMAX = 147
  SET @H05SpeedMAX = 147
  SET @P05SpeedMAX = 147
  SET @C05SpeedMAX = 147
  
  
  SET @L05CurWO = (SELECT TOP 1 L05WO FROM WB.dbo.Line05 WHERE L05WO IS NOT NULL ORDER BY L05Time DESC)
  SET @L05CurCount = (SELECT SUM(dbo.TOD(E05Count)) FROM WB.dbo.Line05 WHERE L05WO = @L05CurWO)
  SET @L05CurQual = (SELECT TOP 1 CASE WHEN dbo.TOD(L05Count)&lt;1 THEN 0 ELSE CASE WHEN dbo.TOD(L05Blowoff)&lt;1 THEN 1 ELSE (1-(dbo.TOD(L05Blowoff)/
                          dbo.TOD(L05Count))) END END FROM WB.dbo.Line05 WHERE L05WO = @L05CurWO ORDER BY L05Time DESC)
  
  
  --Select Current Numbers
      SELECT TOP 1 @L05CurWO AS CurrentL05WorkOrder
                      ,L05TE AS CurrentL05TE
                      ,L05TF AS CurrentL05TF
                      ,@L05CurCount AS CurrentL05Count
                      ,@L05JobScrap AS CurrentL05Scrap
                      ,L05QUAN AS CurrentL05Quantity
                      ,dbo.TOD(L05UP)*100 AS CurrentL05Uptime
                      ,dbo.TOD(C05SPD) AS CurrentL05Speed
                      ,@L05CurQual*100 AS CurrentL05Qual
                      ,CASE WHEN dbo.ToD(L05UP)&gt;0 AND dbo.ToD(C05SPD)&gt;0 AND @L05CurQUAL&gt;0 THEN dbo.ToD(L05UP)*(dbo.ToD(C05SPD)/@L05SpeedMAX)*@L05CurQual ELSE 0 END AS CurrentL05OEE
                      ,dbo.ToD(E05SPD) AS CurrentE05Speed
                      ,dbo.ToD(E05UP)*100 AS CurrentE05Up
                      ,dbo.ToD(E05QUAL)*100 AS CurrentE05Qual
                      ,CASE WHEN dbo.ToD(E05UP)&gt;0 AND dbo.ToD(E05SPD)&gt;0 AND dbo.ToD(E05QUAL)&gt;0 THEN (dbo.ToD(E05UP)*(dbo.ToD(E05SPD)/@E05SpeedMAX)*(1-(dbo.ToD(E05Blowoff)/
                       dbo.ToD(E05Count)))*100) ELSE 0 END AS CurrentE05OEE
                      ,dbo.ToD(H05SPD) AS CurrentH05Speed
                      ,dbo.ToD(H05UP)*100 AS CurrentH05Up
                      ,dbo.ToD(H05QUAL)*100 AS CurrentH05Qual
                      ,CASE WHEN dbo.ToD(H05UP)&gt;0 AND dbo.ToD(H05SPD)&gt;0 AND dbo.ToD(H05QUAL)&gt;0 THEN (dbo.ToD(H05UP)*(dbo.ToD(H05SPD)/@H05SpeedMAX)*(1-(dbo.ToD(H05Blowoff)/
                       dbo.ToD(H05Count)))*100) ELSE 0 END AS CurrentH05OEE
                      ,dbo.ToD(P05SPD) AS CurrentP05Speed
                      ,dbo.ToD(P05UP)*100 AS CurrentP05Up
                      ,dbo.ToD(P05QUAL)*100 AS CurrentP05Qual
                      ,CASE WHEN dbo.ToD(P05UP)&gt;0 AND dbo.ToD(P05SPD)&gt;0 AND dbo.ToD(P05QUAL)&gt;0 THEN (dbo.ToD(P05UP)*(dbo.ToD(P05SPD)/@P05SpeedMAX)*(1-(dbo.ToD(P05Blowoff)/
                       dbo.ToD(P05Count)))*100) ELSE 0 END AS CurrentP05OEE
                      ,dbo.ToD(C05SPD) AS CurrentC05Speed
                      ,dbo.ToD(C05UP)*100 AS CurrentC05Up
                      ,dbo.ToD(C05QUAL)*100 AS CurrentE05Qual
                      ,CASE WHEN dbo.ToD(C05UP)&gt;0 AND dbo.ToD(C05SPD)&gt;0 AND dbo.ToD(C05QUAL)&gt;0 THEN (dbo.ToD(C05UP)*(dbo.ToD(C05SPD)/@P05SpeedMAX)*(1-(dbo.ToD(C05Blowoff)/
                       dbo.ToD(C05Count)))*100) ELSE 0 END AS CurrentC05OEE
      FROM WB.dbo.Line05
      WHERE L05WO = @L05CurWO
      ORDER BY L05Time DESC
  </code></pre>