如何在SQL Server中使用OVER子句中的变量

Question

我想使用一个变量来表示'OVER子句‘语句中使用的行数。到目前为止，我只是通过在字符串中创建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;

这会导致紧跟在'rows‘之后的@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

如果您使用工作查询创建一个存储过程并使用该SP作为源，该怎么办？

Answer 2

我可能会尝试改进这个答案，但是如果您采用使用动态SQL的解决方案，并将其与临时表和"insert into ... exec ...“语法，然后返回到SSIS只需要"select * from @ https://stackoverflow.com/a/24073229/3591870“，SSIS应该能够确定返回的列并生成您的源码。然而，我真的不喜欢您被要求使用动态SQL来解决这个问题。

根据文档http://msdn.microsoft.com/en-us/library/ms189461(v=sql.120).aspx，它确实指定了“无符号整数文字”，所以我认为动态SQL将是唯一的方法。