Rich Rich - 9 months ago 48
SQL Question

Can I use a variable instead of the integer in OVER clause

I have a query that calculates a 50 day moving average from a column Price like this:

select Date,
avg(price) over( order by Date, Date rows between 50 preceding and current row) as moving_avg
from t1

Now I would like to replace the integer 50 by a integer variable to do a loop testing different moving average length.

When I try I get:

Incorrect syntax near '@MA'

Answer Source

Unfortunately not. If you know how to read them, the syntax diagrams included in the SQL documentation are quite thorough.

As you peruse the one for OVER, you'll eventually find that the variant for the PRECEDING specification is <unsigned_value_specification> PRECEDING.

And then lower down:

<unsigned value specification> ::=   
{  <unsigned integer literal> } 

So, unfortunately, your only choice at this time is to use a literal - not a variable, not an expression. When variants (variable vs literal, say) are allowed, the syntax diagrams do tend to make such variants explicitly visible.

The comparable syntax for TOP has:

    TOP (expression) [PERCENT]  
    [ WITH TIES ]  

where as you already know, you can use any expression here.