Как использовать переменную в предложении OVER в SQL Server

Question

Я хотел бы использовать переменную для количества строк, используемых в операторе 'OVER clause'. До сих пор я работал только путем создания оператора sql в строке и затем выполнял его.

Хотя конечная цель также заключается в использовании его в службах SSIS, это не работает, в то время как не распознаются поля в динамическом запросе.

Что работает это:

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;

Число 7 должно быть переменной, поэтому я пытался сделать что-то вроде этого:

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;

Это приводит к синтаксической ошибке в @var_MA_Low сразу после "строк".

То, что работает, это то же утверждение, что и выше, но я не могу использовать его в качестве источника в 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;

Кто-нибудь знает, как передать количество строк в качестве переменной во второй вариант?

Answer 1

Я мог бы попытаться улучшить этот ответ, но если вы возьмете свое решение, которое работает с использованием динамического SQL, и объедините его с временной таблицей и синтаксисом "вставить в... exec ... ", /questions/40655878/hranimaya-protsedura-prevratit-imya-tablitsyi-v-peremennuyu-tablitsyi/40655898#40655898, а затем верните обратно в SSIS только "select * from @holdertable", SSIS должна быть в состоянии определить возвращаемые столбцы и сгенерировать ваш источник. Мне не очень нравится тот факт, что вы должны использовать динамический SQL для решения этой проблемы.

Согласно документам, http://msdn.microsoft.com/en-us/library/ms189461(v=sql.120).aspx, он действительно указывает "целочисленный литерал без знака", поэтому я думаю, что динамический SQL будет быть единственным способом.

Answer 2

Что делать, если вы создаете хранимую процедуру с рабочим запросом и используете этот SP в качестве источника?