title: Using a SQL parameter when querying iHistorian
body: <p>I am using MS SQL to query iHistorian through a linked server.  I want to loop through different tagnames from a SQL table.  The problem I have is adding the parameter to the ihistorian query.  The code is as follows.  I haven't included the declare on tagname as its not relevant at this point.</p>
  
  <pre><code>    SELECT timestamp AS Interval, Left(Right(TagName,Len(TagName)-5),Len(TagName)-10) as TagName,
  CONVERT(decimal(10, 3), ROUND(value, 3)) As Value, quality 
  FROM 
     OPENQUERY(IHISTORIAN,'
                  SET starttime =''yesterday '', endtime =''yesterday +1h''  
                  SELECT timestamp, tagname, value, ihRawData.quality 
                  FROM ihRawData
                  WHERE tagname = '+ @tagname +' 
                  AND timestamp BETWEEN ''timestamp'' and ''timestamp'' 
                  AND SamplingMode =Calculated
                  AND CalculationMode =Average
                  AND IntervalMilliseconds =1h
                  ORDER BY tagname, timestamp')
                  end
  </code></pre>
  
  <p>The problem I am having is at the <code>WHERE tagname = '+ @tagname +'</code>  point.  The query doesn't like the  <code>'+ @tagname +'</code>.  It underlines the first <code>+</code> and says a <code>)</code> is expected.  </p>
  
  <p>I basically want to set the tagname independently of the historian query as stated earlier.</p>
  
  <p>Full Query --</p>
  
  <pre><code>use Optimiser_Test;
  
  declare @tagname varchar(32)
  --declare @rowcount int
  --declare @maxrows int
  --select @maxrows = count(*) from OptimiserTags
  
  --set @rowcount = 0
  
  set @tagname = 'UMIS.99FE1100.F_CV'
  
  --while @rowcount &lt; @maxrows
  begin
  
  
  SELECT timestamp AS Interval, Left(Right(TagName,Len(TagName)-5),Len(TagName)-10) as TagName,
  CONVERT(decimal(10, 3), ROUND(value, 3)) As Value, quality 
  FROM 
     OPENQUERY(IHISTORIAN,'
                  SET starttime =''yesterday '', endtime =''yesterday +1h''  
                  SELECT timestamp, tagname, value, ihRawData.quality 
                  FROM ihRawData
                  WHERE tagname = REPLACE(@tagName, '''','')
                  AND timestamp BETWEEN ''timestamp'' and ''timestamp'' 
                  AND SamplingMode =Calculated
                  AND CalculationMode =Average
                  AND IntervalMilliseconds =1h
                  ORDER BY tagname, timestamp')
                  end
  </code></pre>
  
  <p>NEW UPDATE --</p>
  
  <pre><code>use Optimiser_Test;
  
  
  declare @tagname varchar(32)
  declare @fullquery varchar(1000)
  
  set @fullquery = 'SELECT timestamp AS Interval, Left(Right(TagName,Len(TagName)-5),Len(TagName)-10) as TagName,
  CONVERT(decimal(10, 3), ROUND(value, 3)) As Value, quality 
  FROM 
     OPENQUERY(IHISTORIAN,
                  SET starttime =''yesterday '', endtime =''yesterday +1h''  
                  SELECT timestamp, tagname, value, ihRawData.quality 
                  FROM ihRawData
                  WHERE tagname = '''+ @tagname +'''
                  AND timestamp BETWEEN ''timestamp'' and ''timestamp'' 
                  AND SamplingMode =Calculated
                  AND CalculationMode =Average
                  AND IntervalMilliseconds =1h
                  ORDER BY tagname, timestamp'''''
  
  begin
  set @tagname = 'UMIS.99FE1100.F_CV'
  exec (@fullquery)
  
  end
  </code></pre>
  
  <p>New Update 2 --</p>
  
  <p>If I run the query with the tagname specified in the query as below I get the following result.  Which is correct.</p>
  
  <pre><code>use Optimiser_Test;
  
  SELECT timestamp AS Interval, Left(Right(TagName,Len(TagName)-5),Len(TagName)-10) as TagName,
  CONVERT(decimal(10, 3), ROUND(value, 3)) As Value, quality 
  FROM 
     OPENQUERY(IHISTORIAN,'
                  SET starttime =''yesterday '', endtime =''yesterday +1h''  
                  SELECT timestamp, tagname, value, ihRawData.quality 
                  FROM ihRawData
                  WHERE tagname = UMIS.99FE1100.F_CV
                  AND timestamp BETWEEN ''timestamp'' and ''timestamp'' 
                  AND SamplingMode =Calculated
                  AND CalculationMode =Average
                  AND IntervalMilliseconds =1h
                  ORDER BY tagname, timestamp')
  </code></pre>
  
  <p>Result</p>
  
  <p><img src="https://i.stack.imgur.com/q3azf.png" alt="enter image description here"></p>
  
  <p>After numerous back and forths with James S below and some further work with my colleagues I got to this which returns what I need.</p>
  
  <pre><code>Declare @SQLString varchar(1000)
  Declare @TAGS VarChar(1000)
  
  Set @Tags = '"UMIS.99FE1100.F_CV"'
  
  SET @SQLString = 'SELECT timestamp AS Interval, Left(Right(TagName,Len(TagName)-5),Len(TagName)-10) as TagName,
  CONVERT(decimal(10, 3), ROUND(value, 3)) As Value, quality 
  FROM 
     OPENQUERY(IHISTORIAN,''
                  SET starttime =''''yesterday '''', endtime =''''yesterday +1h'''',
                           SELECT timestamp, tagname, value, ihRawData.quality 
                  FROM ihRawData
                  WHERE tagname = ' + @Tags + '
                             and IntervalMilliseconds =1h,  
                             and CalculationMode =Average,
                             and SamplingMode =Calculated
                  ORDER BY tagname, timestamp'')
                             '
  
  Print @SQLString
  Print @Tags
  Exec (@SQLString)
  </code></pre>