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?

score 0 · Answer 1 · answered Jun 14 '14 at 12:36

0

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

answered Jun 14 '14 at 12:36

Roopesh

279
2
7

Not yet looked into that possibility. I am investigating the SSIS possibilities while somebody mentioned almost anything you can do with T-SQL can be done by SSIS. So I am trying to avoid using SP's as long as possible. – xSter Jun 14 '14 at 12:46
In the mean time I tried SP as possible alternative, but this leaves the same challenge. SSIS source is not able to retrieve the columns to map to an output. – xSter Jun 14 '14 at 14:59

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

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.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 15 '14 at 04:02

Bruce Dunwiddie

2,888
1
15
20

Thanks, this will indeed do the trick. Now I combined an 'Execute T-Sql Statement Task' with a 'Data Flow Task'. Not the most pretty solution but it works. – xSter Jun 17 '14 at 18:22
You should have been able to do it in just a single data flow task. – Bruce Dunwiddie Jun 18 '14 at 02:44

How to use variable in OVER clause in SQL Server

2 Answers2