SQL Concatenate a Variable with a string inside of a select statement in a sproc

Question

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.

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)

    ,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)))>0 AND AVG(CAST(LEFT(E05SPD, (CHARINDEX(',', E05SPD, 5) + 6)) AS decimal(11,6)))>0 AND 
            AVG(CAST(LEFT(E05QUAL, (CHARINDEX(',', E05QUAL, 5) + 6)) AS decimal(11,6)))>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

And what I'd be looking to do in short would be:

    SELECT AVG(E+@P1+SPD) AS @P2+Speed FROM Test.Dbo.Line+@P1 WHERE @P3

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.

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)<1 THEN 0 ELSE CASE WHEN dbo.TOD(L05Blowoff)<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)>0 AND dbo.ToD(C05SPD)>0 AND @L05CurQUAL>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)>0 AND dbo.ToD(E05SPD)>0 AND dbo.ToD(E05QUAL)>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)>0 AND dbo.ToD(H05SPD)>0 AND dbo.ToD(H05QUAL)>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)>0 AND dbo.ToD(P05SPD)>0 AND dbo.ToD(P05QUAL)>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)>0 AND dbo.ToD(C05SPD)>0 AND dbo.ToD(C05QUAL)>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

score 2 · Accepted Answer · edited May 23 '17 at 10:24

Start with

Create Function dbo.ToD(@val as varchar(max)) Returns Decimal(11, 6) As
Begin
  Return Cast(Left(@val, (CharIndex(',', @val, 5) + 6)) As Decimal(11, 6))
End

I think the next step is dynamic sql something like this should get you on the right track. (If @Col comes from user input, this has sql injection vulnerabilities):

This is completely untested, so there will be silly errors. I've also probably misunderstood exactly which bits change from query to query and which remain the same. I'm not 100% sure the mechanism for setting a parameter via dynamic sql works, but I read it at How to get sp_executesql result into a variable?. You might need to use a temporary table instead.

Declare 
  @Col nvarchar(max) = '05',
  @sql nvarchar(max),
  @params nvarchar(max),
  @CurWO int

-- Dynamically getting one of the current values
Set @sql = N'Set @CurWO = (' + 
           N'Select Top 1 L' + @Col + N'WO' +
           N' From WB.dbo.Line' + @Col + 
           N' Where L' + @Col + N'WO Is Not Null' +
           N' Order By L' + @Col + N'Time Desc)'

-- For diagnosing the inevitable errors
Print @sql

Set @params = N'@CurWO int output'

Exec sp_executesql @sql, @params, @CurWO Output

-- Dynamically executing the main query
Set @sql = N'Select Top 1 @CurWO As CurrentL' + @Col + N'WorkOrder,' + -- @CurWO from last query
           N' L' + @Col + N'TE AS CurrentL' + @Col + N'TE,' +
           N' L' + @Col + N'TF AS CurrentL' + @Col + N'TF,' +
           ...
           N' From WB.dbo.Line' + @Col +
           N' Where L' + @Col + N'WO = @CurWO' +
           N' Order By L' + @Col + N'Time Desc'

-- For diagnosing the inevitable errors
Print @sql

Set @params = N'@CurWO int'

Exec sp_executesql @sql, @params, @CurWO

I had created a function for the date to JD Edwards current day conversion side of this, not sure why I hadn't thought of this too...Thanks! — Steve, Nov 13 '12 at 15:36
Once you have this, put the rewritten query up, and we can look at simplifying further. — Laurence, Nov 13 '12 at 15:45
Thanks a million!, my home Hyper-V lab is down (moving) so I'll give it a run though when I get to the office in the morning. — Steve, Nov 14 '12 at 00:03

SQL Concatenate a Variable with a string inside of a select statement in a sproc

1 Answers1