SQL: Is there a way to iteratively DECLARE a varia

问题:

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

At the end I would have 10 variables:

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

Update:

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.

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.

Example input:

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.

回答1:

I use a UDF to create Dynamic Date Ranges.

For exanple

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

Returns

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

The 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) 

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

回答2:

Whenever you're looking to generate lists of things that differ numerically (incrementally, etc.), think of using a Tally table (a table of numbers). Generating dates is a great application for a tally table:

declare @startDate date = '20160101'
declare @endDate date = '20160601'

select N
    , dateadd(year, (N - 1) * -1, @startDate) as StartDate
    , dateadd(year, (N - 1) * -1, @endDate) as EndDate
from tally -- Follow the link above for info on how to create this table
where N <= 4
order by N desc

Result:

N   StartDate   EndDate
4   2013-01-01  2013-06-01
3   2014-01-01  2014-06-01
2   2015-01-01  2015-06-01
1   2016-01-01  2016-06-01

Making calculations with a tally table in a query will often be much more efficient than loops, cursors, or dynamic SQL. In this case, compared to other answers presented, I'd say it's easier to program and maintain as well.

After seeing several of the other answers, I must say that I strongly encourage you to NOT create tons of numbered variables to hold these values. This would often be poor style in other languages where you might use an array or list or some other data structure, let alone SQL where sets and the means to manipulate and store them are fundamental to the language itself.

Perhaps I'm not seeing your particular use case, but even if you create these numbered variables using code, you'll then have to write more code to actually call these variables in any subsequent logic or calculations.

回答3:

First, create a numbers table like this.

declare @numbers table(n int)
insert into @Numbers(N)
select top 1000 row_number() over(order by t1.number) as N
from   master..spt_values t1 
       cross join master..spt_values t2

then create dates like this

    declare @iniDate date
    declare @endDate date
    delcare @limitDate date

    set @iniDate='20160101'
    set @enddate='20160106'
    set @limitDate ='20130101'

    select dateadd(yy,-1*(n-1),@inidate), dateadd(yy,-1*(n-1),@enddate) 
    from @numbers where (n-1)<=datediff(yy, @limitDate, @inidate)

EDIT: After new request, try this:

declare @iniDate date
declare @endDate date
declare @iniLimitDate date
declare @endLimitDate date

set @iniDate='20140201'
set @endDate='20140206'
set @iniLimitDate ='20130101'
set @endLimitDate ='20161231'    

select datefromparts(year(@iniLimitDate)+n-1,month(@iniDate), day(@iniDate))
    ,datefromparts(year(@iniLimitDate)+n-1,month(@endDate), day(@endDate))
from @numbers 
where  year(@iniLimitDate)+n-1<=year(@endlimitdate)
    and year(@iniLimitDate)+n-1<>year(@enddate)

NOTE: It needs to be fixed for feb 29

回答4:

John has a fancier solution than my simple example here, but this one doesn't need a separate UDF. In case you don't have permissions for those or something.

DECLARE @startDate DATETIME, @endDate DATETIME, @tmpStartDate DATETIME, @tmpEndDate DATETIME 
SET @startDate = '1/1/2016'
SET @endDate = '6/1/2016'


SET @tmpStartDate = @startDate
SET @tmpEndDate = @endDate
DECLARE @dateTbl TABLE (startDate DATETIME, endDate DATETIME)

WHILE (DATEPART(YEAR, @tmpStartDate) >= 2013)
BEGIN
    INSERT INTO @dateTbl VALUES (@tmpStartDate, @tmpEndDate)
    SET @tmpStartDate = DATEADD(year, -1, @tmpStartDate)
    SET @tmpEndDate = DATEADD(year, -1, @tmpStartDate)
END

SELECT * FROM @dateTbl

回答5:

Would something like this work for you? It uses dynamic SQL to build a query. I don't know about your date formats, but I used cast to sever the time portion from the standard getDate() function just in case.

DECLARE @iter int = 1
Declare @SQL VARCHAR(MAX)

WHILE @iter<11
BEGIN
SET @SQL = ISNULL(@SQL,'') + ' DECLARE @newdate'+ CAST(@iter AS VARCHAR) + ' DATE = ' + CAST(CAST(GETDATE() AS DATE) AS VARCHAR) + ' ' 
PRINT (@SQL)
SET @iter = @iter + 1
END

SET @SQL = @SQL + ' SELECT * FROM blah'
EXEC @SQL

回答6:

Other methods mentioned are much better for what you are trying to do, but in terms of answering the question "Is there a way to iteratively DECLARE a variable?", you could do something like what I have below using dynamic SQL. Basically you would create a string, using a loop, that contains your declare statements. Then you would create an additional string (or strings) to use them. In this example, I'm simply creating the variables and setting them to today's date. Then I select each variable.

DECLARE @iter INT = 1, @SQL VARCHAR(MAX) = '', @MoreSQL VARCHAR(MAX) = '';

WHILE @iter < 11
BEGIN
    SET @SQL += 'DECLARE @NewDate' + CAST(@iter AS VARCHAR(2)) + ' DATE = GETDATE() '

    SET @iter += 1
END

SET @iter = 1

WHILE @iter < 11
BEGIN
    SET @MoreSQL  += 'SELECT @NewDate' + CAST(@iter AS VARCHAR(2)) + ' '

    SET @iter += 1
END

SET @SQL += @MoreSQL

EXEC (@SQL)

回答7:

To get start/end ranges only for last 4 years:

DECLARE @START DATETIME, @END DATETIME

SET @START='2016-01-01'
SET @END='2016-06-01'

DECLARE @DATES TABLE (STARTDATE DATETIME, ENDDATE DATETIME)

INSERT INTO @DATES (STARTDATE, ENDDATE)
SELECT @START,@END UNION
SELECT DATEADD(YY,-1,@START),DATEADD(YY,-1,@END) UNION
SELECT DATEADD(YY,-2,@START),DATEADD(YY,-2,@END) UNION
SELECT DATEADD(YY,-3,@START),DATEADD(YY,-3,@END)

SELECT * FROM @DATES ORDER BY STARTDATE

To get all dates in between:

DECLARE @START DATETIME, @END DATETIME

SET @START='2016-01-01'
SET @END='2016-06-01'

DECLARE @CURDATE DATETIME
DECLARE @DATES TABLE (DATEVAL DATETIME)

SET @CURDATE=@START

WHILE @CURDATE<=@END
BEGIN
    INSERT INTO @DATES (DATEVAL)
    SELECT @CURDATE UNION
    SELECT DATEADD(YY,-1,@CURDATE) UNION
    SELECT DATEADD(YY,-2,@CURDATE) UNION
    SELECT DATEADD(YY,-3,@CURDATE) 

    SET @CURDATE=DATEADD(DD,1,@CURDATE)
END

SELECT * FROM @DATES ORDER BY DATEVAL

To get all years from @START to 2013...

DECLARE @START DATETIME, @END DATETIME

SET @START='2016-01-01'
SET @END='2016-06-01'

DECLARE @DATES TABLE (STARTDATE DATETIME, ENDDATE DATETIME)

DECLARE @CURYEAR INT
SET @CURYEAR=YEAR(@START)

WHILE @CURYEAR>= 2013 
BEGIN
    INSERT INTO @DATES (STARTDATE, ENDDATE)
    SELECT @START,@END 

    SET @START = DATEADD(YY,-1,@START)
    SET @END = DATEADD(YY,-1,@END)
    SET @CURYEAR=YEAR(@START)
END
SELECT * FROM @DATES ORDER BY STARTDATE