从Firebird到SQL Server的过程

I need to create stored-procedure which returns one row for every week between two dates. I create this procedure in Firebird, but I can't achieve the same thing in SQL Server 2012.

我需要创建存储过程，在两个日期之间每周返回一行。我在Firebird中创建了这个过程，但是我不能在SQL Server 2012中实现同样的功能。

I try to use Stored-procedure, but it cannot be called from select statement (I need to use result in union with another query)

我尝试使用存储过程，但是不能从select语句调用它(我需要使用result in union与另一个查询)

I try to use function, but it return only one (last) value (week).

我尝试使用函数，但它只返回一个(最后一个)值(周)。

How can I rewrite this stored procedure for SQL Server 2012?

如何重写SQL Server 2012的存储过程?

begin
TydenT = StartDate;
  while (TydenT < EndDate)
  do begin
  for select first 1
            EXTRACT(YEAR from cast(ib_datetostring(:TydenT, 'dd.mm.yyyy') as date)),
            EXTRACT(WEEK from cast(ib_datetostring(:TydenT, 'dd.mm.yyyy') as date))
        from SecurityUsers
  into :Rok, :Tyden
  do
    begin
    suspend;
    end
        TydenT = TydenT + 7;
        end
end

StartDate and EndDate are input parameters. Rok and Tyden are output paramaters and TydenT are variable.

StartDate和EndDate是输入参数。韩国和泰登是输出参数，泰登是变量。

EDIT. If I use (41952 and 41975 are date in float)

编辑。如果我使用(41952和41975是浮点数)

select
    *
from StoredProcedure(41952, 41975)

then I want to get:

然后我想得到:

1 个解决方案

#1

You could use an user-defined function to do this.

您可以使用用户定义的函数来实现这一点。

This is just an example, it can almost certainly be written better, but it should given you an idea I hope. I used strings for input dates as I'm not sure how to convert the float dates (which I guess is a FireBird or Delphi thing).

这只是一个例子，它几乎肯定可以写得更好，但它应该给你一个想法，我希望。我使用字符串作为输入日期，因为我不知道如何转换浮点日期(我猜是FireBird或者Delphi之类的)。

CREATE FUNCTION dbo.getWeekNumbers (@StartDate date, @EndDate date)
RETURNS table
AS
RETURN (
    WITH cte (d) AS (
       SELECT DATEADD(day, 1, @StartDate) AS d
       UNION ALL
       SELECT DATEADD(day, 1, d) 
       FROM cte
       WHERE d < @EndDate
    )
    SELECT DISTINCT 
       DATEPART(year, d) AS y, 
       DATEPART(week, d) AS w 
    FROM cte
);

And call it like this:

这样称呼它:

SELECT * FROM dbo.getWeekNumbers('2014-10-01','2014-10-20')

Result:

结果:

y           w
----------- -----------
2014        40
2014        41
2014        42
2014        43