How to use variable in OVER clause in SQL Server

Question

I would like to use a variable for the number of rows used in an 'OVER clause' statement. Up to now I only get it working by creation of the sql statement in a string and then execute it.

While the final purpose is to also use it in SSIS this does not work while that does not recognizes the fields in the dynamic query.

What works is:

select 
    [GUID_Fund], [Date], [Close], 
    avg([Close]) over (order by [GUID_Fund], [Date] rows 7 preceding) as MA_Low
from fundrates
group by [GUID_Fund], [Date], [Close] 
order by [GUID_Fund] asc, [Date] desc;

The number 7 needs to be a variable so I was trying to do something like this:

declare @var_MA_Low as int;

select distinct @var_MA_Low = [Value1] 
from Variables  
where [Name]='MA_Low';

select 
    [GUID_Fund], [Date], [Close], 
    avg([Close]) over (order by [GUID_Fund], [Date] rows @var_MA_Low preceding) as MA_Low
from fundrates
group by [GUID_Fund], [Date], [Close] 
order by [GUID_Fund] asc, [Date] desc;

This results in a syntax error at @var_MA_Low just after 'rows'.

What works is the same statement as above, but than I cannot use it as source in SSIS:

declare @MA as nvarchar(max);
declare @var_MA_Low as nvarchar(max);
select distinct @var_MA_Low = [Value1] from Variables where [Name]='MA_Low';
set @MA = N'select [GUID_Fund], [Date], [Close], avg([Close])
        over (order by [GUID_Fund], [Date] rows '+@var_MA_Low+' preceding) as MA_Low 
    from fundrates
    group by [GUID_Fund], [Date], [Close] order by [GUID_Fund] asc, [Date] desc;'

execute sp_executesql @MA;

Has anybody an idea how to pass the number of rows as a variable into the second option?

Answer 1

what if you create a stored procedure with working query and use that SP as source?

Answer 2

I might try to improve this answer, but if you take your solution that works using the dynamic SQL and combine it with a temp table and the "insert into ... exec ... " syntax, https://stackoverflow.com/a/24073229/3591870 , and then return back to SSIS just the "select * from @holdertable", SSIS should be able to determine the columns being returned and generate your source. I don't really like the fact of you being required to use dynamic SQL to solve this however.

According to the docs, http://msdn.microsoft.com/en-us/library/ms189461(v=sql.120).aspx , it really does specify "unsigned integer literal", so I think dynamic SQL is going to be the only way.