SQL:有没有办法迭代声明一个变量? [英] SQL: Is there a way to iteratively DECLARE a variable?

问题描述

有没有办法在 SQL 中完成这样的事情:

Is there a way to accomplish something like this in SQL:

DECLARE @iter = 1

WHILE @iter<11
BEGIN
DECLARE @('newdate'+@iter) DATE = [some expression that generates a value]
SET @iter = @iter + 1
END

最后我会有 10 个变量:

At the end I would have 10 variables:

@newdate1
@newdate2
@newdate3
@newdate4
@newdate5
@newdate6
@newdate7
@newdate8
@newdate9
@newdate10

更新:

根据评论，我想我应该说明为什么我要这样做.我正在使用 Report Builder 3.0.我将制作一份报告，其中输入将是 start date 和 end date(除了一个其他参数).这将生成日期范围之间的数据.但是，用户还希望检查集合 2013 -> 当前年份中所有其他年份的相同日期范围.

Based on a comment, I think I should specify why I want to do this. I am working with Report Builder 3.0. I am going to make a report where the input will be a start date and an end date (in addition to one other parameter). This will generate data between the date range. However, the user also wants to check the same date range for all other years in the set 2013 -> current year.

棘手的部分是:用户可以在 2013 年和当前年份之间的 任何 年中输入日期范围，我需要返回输入年份的数据以及其他年份的数据.例如，如果用户输入 1/1/2014 - 6/1/2014，那么我需要返回相同的范围，但对于 2013、2015 和 2016 年.

The tricky part is this: the user can enter a date range in any year between 2013 and the current year and I need to return data for the input year and also data for the other years. For example, if the user enters in 1/1/2014 - 6/1/2014 then I need to return the same range but for the years 2013, 2015, and 2016.

示例输入:

1/1/2016 - 6/1/2016

报告必须为这些值生成数据:

Report must generate data for these values:

1/1/2013 - 6/1/2013
1/1/2014 - 6/1/2014
1/1/2015 - 6/1/2015
1/1/2016 - 6/1/2016

如果有更好的方法可以做到这一点，我会全力以赴.

If there is a better way to do this, I'm all ears.

推荐答案

我使用 UDF 来创建动态日期范围.

I use a UDF to create Dynamic Date Ranges.

例如

Select DateR1=RetVal,DateR2=DateAdd(MM,5,RetVal) from [dbo].[udf-Create-Range-Date]('2013-01-01','2016-01-01','YY',1) 

退货

DateR1      DateR2
2013-01-01  2013-06-01
2014-01-01  2014-06-01
2015-01-01  2015-06-01
2016-01-01  2016-06-01

UDF

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1) 

精简版 - 非 UDF这可以注入到您的 SQL 中

Stripped Down version - NON UDF This can be injected into your SQL

Declare @startdate Date ='1/1/2014'   -- user supplied value
Declare @enddate Date = '6/1/2014'    -- user supplied value

Declare @DateFrom Date = cast('2013-'+cast(month(@StartDate) as varchar(10))+'-'+cast(Day(@StartDate) as varchar(10)) as date)
Declare @DateTo Date   = cast(cast(Year(GetDate()) as varchar(10))+'-'+cast(month(@enddate) as varchar(10))+'-'+cast(Day(@enddate) as varchar(10)) as date) 
Declare @Incr int = DateDiff(MM,@startdate,@enddate)  -- made to be dynamic based on the user supplied dates

Declare @DateRange Table (DateR1 date,DateR2 Date)

;with DateTable As (
    Select DateFrom = @DateFrom
    Union All
    Select DateAdd(YY, 1, df.dateFrom)
    From DateTable DF
    Where DF.DateFrom < @DateTo
)
Insert into @DateRange(DateR1,DateR2) Select DateR1=DateFrom,DateR2=DateAdd(MM,@Incr,DateFrom) From DateTable option (maxrecursion 32767)

Select * from @DateRange

这篇关于SQL:有没有办法迭代声明一个变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！