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

Question

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

select Date,  
       price, 
       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'

I'm not certain on the answer since I haven't run into this before, but it seems like this would be similar to [using a variable for TOP in sql server](http://stackoverflow.com/questions/1927450/use-variable-with-top-in-select-statement-in-sql-server-without-making-it-dynami). In order to do that you have to put the variable in parantheses. Maybe try `rows between (@MA) preceding and current row` and see if that makes it happy. — JNevill, Oct 28 '16 at 13:45
@JNevill Good thinking but I just tried `(50) and (@MA) ` and it definitely doesn't like parenthesis in either case — Rich, Oct 28 '16 at 13:50
Shucks. Sounds like you'll have to go with dynamic sql then. That's always a party pooper :( — JNevill, Oct 28 '16 at 13:54

score 2 · Accepted Answer · answered Oct 28 '16 at 13:53

2

I was hoping that (Select @MA) would work, but alas no luck

Perhaps you can go dynamic

Declare @MA int = 50

Declare @SQL varchar(max) ='Select Date, price, avg(price) over( order by Date, Date rows between '+cast(@MA as varchar(25))+' preceding and current row) as moving_avg from t1 '
Exec(@SQL)

answered Oct 28 '16 at 13:53

John Cappelletti

79,615
7
44
66

@Rich Happy to help. – John Cappelletti Oct 29 '16 at 18:59

score 1 · Answer 2 · answered Oct 28 '16 at 13:52

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.

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

2 Answers2