title: Define a parameter for searching on days DATEADD / datediff - SQL server 2005
body: <p>So I have now a grouping of values by their currencies and values, which brings out values for the last 30 days, however I would like to search by date, eventually using a parameter from another table.</p>
  
  <pre><code>;WITH cte AS (SELECT ROW_NUMBER() OVER (ORDER BY date_loaded Asc) AS n, value, 
  date_loaded, cast(round(value * 0.0001  / 100 * 0.5 + (value * 0.0001),4)AS dec(12,4)) as buy_rate,
          cast(round(value * 0.0001  / 100 * -0.5 + (value * 0.0001), 4) AS dec(12,4)) as sell_rate 
  
  FROM texchange_rate WHERE source_currency_code = @source_currency_code 
  and target_currency_code = @target_currency_code
  and date_loaded between dateadd(day, datediff(day, 0 ,getdate())-30, 0) and getdate())
  
  SELECT t2.value, t2.date_loaded, @source_currency_code as source_currency_code,
  @target_currency_code as target_currency_code, t2.buy_rate, t2.sell_rate
      FROM cte t2 LEFT JOIN cte t1 ON t2.n = t1.n + 1
      WHERE t2.value &lt;&gt; ISNULL(t1.value, -1)
  
      order by date_loaded desc
  
  END
  </code></pre>
  
  <p>I want to define the amount of days that dateadd searches on from a separate table, is this possible? E.g.</p>
  
  <p>from </p>
  
  <pre><code>dateadd(day, datediff(day, 0 ,getdate())-30, 0) and getdate())
  </code></pre>
  
  <p>to</p>
  
  <pre><code>dateadd(day, datediff(day, 0 ,getdate())@dayparameter, 0) and getdate())
  </code></pre>
  
  <p>Just to get this working, I've attempted declaring @dayparameter (similar to here - <a href="http://msdn.microsoft.com/en-us/library/ms186819%28v=sql.100%29.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms186819%28v=sql.100%29.aspx</a>, although this is for server 2008) at the start of the stored procedure, and placing it inside dateadd gives the error </p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Procedure proc_getCurrencyHistory, Line 48
    Incorrect syntax near '@dayparameter'.</p>
  </blockquote>
  
  <p>Hope this makes sense.</p>